How do I add a new column (in transform data) that fills down data in the column from a single cell in an Excell Sheet?

Question

I have a Plant name and Local Currency in a cell at the top of an excell sheet above the table of data with actions. I want to add a custom columns to the data set that pulls in the plant name and currency and assigns it to each action in the data set.

The excell sheet looks like

Answer 1

Hi @VAN WIJK, ANSIE

One way to go:

let  
    Source = {null,null,"AUD","Plant",null},  
    ThirdItemInSourceList = List.First( List.Skip(Source, 2) ),  
    FourthItemInSourceList = List.First( List.Skip(Source, 3) )  
in  
    FourthItemInSourceList  

Another one:

let  
    Source = {null,null,"AUD","Plant",null},  
    NoNull = List.RemoveNulls(Source),  
    FirstIemInNoNull = List.First(NoNull),  
    LastItemInNoNull = List.Last(NoNull)  
in  
    LastItemInNoNull  

Note: there's another way to access items in Lists or records in Tables by their #index, the above approaches are recommended though

Answer 2

@VAN WIJK, ANSIE

In this .zip 2 files: an Excel workbook, a PowerBI Desktop project

• Download the .zip
• Extact the files in a folder
• Open the .pbix
• Go to Transform Data
• Edit query QueryExample to change the path in the Source step

Answer 3

@VAN WIJK, ANSIE
One important addition: As I don't know what your data consist of exactly, in the QueryExample I didn't add at the end a Change Types step

As you're going (I suppose) to load the data to the PBI Model, Changing the Types of the columns is almost a must, otherwise they'll end-up as type Text in the Model => No aggregation will be possible