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?

VAN WIJK, ANSIE 41 Reputation points
2021-07-13T10:53:54.337+00:00

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
114251-example.jpg

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
35,767 questions
{count} votes

Accepted answer
  1. Lz._ 8,986 Reputation points
    2021-07-19T10:15:15.88+00:00

    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


2 additional answers

Sort by: Most helpful
  1. Lz._ 8,986 Reputation points
    2021-07-13T12:41:22.72+00:00

    @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

    114198-demo.png


  2. Lz._ 8,986 Reputation points
    2021-07-13T12:55:18.487+00:00

    @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