Forum Discussion

JustDavid's avatar
JustDavid
Helper IV
10 months ago
Solved

Dynamically combine Headers from each table

PQ Gurus,

 

Apologies in advanced that I can't share sample pbix file as i don't know how to create a "table" in each row.

 

Hopefully my screenshot can show you and that you're able to understand what I desired to achieve.

 

This is the result of one of the applied steps (call it 'PromoteHeader').

 

As you can see, in the [Data] column (denotes by the top right of screenshot), I have Table "result" on each row (what is the correct terminology for this so that everyone understand? Table Row?). And when I click on each of that Table "result", I'd have a preview of the data.

 

What I'd like to ahieve at the end result is to get a distinct column/headers names as list for expansion later on.

 

In order to do that, the logic is:

  1. Loop through each Table row.
  2. On that table, get the header column names.
  3. Combine all the headers and get the unique header names in which I'd use this to expand in my later step.

Need Help
Show and Tell
  • jgeddes's avatar
    jgeddes
    10 months ago

    This might work for you...

    let
    Source = 
    #table(
        type table [Name=nullable text, Data = table],
        {
            {"File1", #table({"Column1", "Column2"},{{"R1C1", "R1C2"},{"R2C1", "R2C2"}})},
            {"File2", #table({"Column1", "Column3"},{{"R1C1", "R1C2"},{"R2C1", "R2C2"}})},
            {"File3", #table({"Column2", "Column4"},{{"R1C1", "R1C2"},{"R2C1", "R2C2"}})}
        }
    ),
    Add_Header_Names_List = Table.AddColumn(Source, "HeaderNames", each Table.ColumnNames([Data]), type list),
    Get_Distinct_Header_Names = List.Distinct(List.Combine(Add_Header_Names_List[HeaderNames]))
in
    Get_Distinct_Header_Names

     

1 Reply

  • jgeddes's avatar
    jgeddes
    Super User
    10 months ago

    This might work for you...

    let
    Source = 
    #table(
        type table [Name=nullable text, Data = table],
        {
            {"File1", #table({"Column1", "Column2"},{{"R1C1", "R1C2"},{"R2C1", "R2C2"}})},
            {"File2", #table({"Column1", "Column3"},{{"R1C1", "R1C2"},{"R2C1", "R2C2"}})},
            {"File3", #table({"Column2", "Column4"},{{"R1C1", "R1C2"},{"R2C1", "R2C2"}})}
        }
    ),
    Add_Header_Names_List = Table.AddColumn(Source, "HeaderNames", each Table.ColumnNames([Data]), type list),
    Get_Distinct_Header_Names = List.Distinct(List.Combine(Add_Header_Names_List[HeaderNames]))
in
    Get_Distinct_Header_Names