Dynamically combine Headers from each table

[JustDavid]

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.

[jgeddes · Accepted solution]

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