Power Query Dynamically add Extra Columns

[Anonymous]

Hi Team,

I have multiple excel sheet sample as this

I am trying to load all these sheets into power bi and doing some transformation to get this table

Here i am picking all the Class type from first sheet that is book1, Now when i am adding one more extra column into book2 like this:

This column is not present in book1 and since i am merging the sheets and taking data from first sheet by default this class in absent in the final table.
Can you guys help me add this extra column into Power bi table dynamically so that even if i add any extra columns to any other sheet lets say book 3 or book 4, the extra class would automatically pick.

I am adding all sheets and power bi file in the comment section.

Thanks

 

[Anonymous]

Hi @Anonymous ,

Based on this:

This column is not present in book1 and since i am merging the sheets and taking data from first sheet by default this class in absent in the final table.

Did you try to change the kind of merge? You may use Right outer or Full outer instead.

Refer to:

Merge queries overview - Power Query | Microsoft Docs

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Eyelyn,

When i am taking the data from a folder, all the sheets are appending with first not Merging,

Do we have any option like this in Appending Queries?

Thanks

[Anonymous]

Hi Team,
I am not able to add files so i am adding the transformation steps here:
""
let
Source = Folder.Files("C:\Users\amitkumar.t\OneDrive - Flatworld Solutions Pvt Ltd\Desktop\Test2"),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content])),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Extension] = ".xlsx")),
#"Expanded Custom" = Table.ExpandTableColumn(#"Filtered Rows", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"Custom.Data", "Name"}),
Collist = Table.ColumnNames(Table.Combine(#"Removed Other Columns"[Custom.Data])),
Custom1 = #"Removed Other Columns",
#"Expanded Custom.Data" = Table.ExpandTableColumn(Custom1, "Custom.Data", Collist),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.Data",{"Name"}),
#"Transposed Table" = Table.Transpose(#"Removed Columns"),
#"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Merged"),
#"Added Custom1" = Table.AddColumn(#"Merged Columns", "Custom", each if [Column4] = "Year" then [Column4]
else if [Column4] = "Period" then [Column4]
else if [Column4] = "ID" then [Column4]
else [Merged]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Merged"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Custom", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83"}),
#"Transposed Table1" = Table.Transpose(#"Reordered Columns"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
#"Filtered Rows1" = Table.SelectRows(#"Promoted Headers", each ([Year] <> "Year")),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows1", {"Year", "Period", "ID"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Class type"}})
in
#"Renamed Columns"

""

[KT_Bsmart2gethe]

Hi @Anonymous ,

In order to achieve what you want (i.e. dynamically pick up additional columns from source)

You will need update the code below:

 From:

#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows1", {"Year", "Period", "ID"}, "Attribute", "Value"),

To:

#"Unpivoted Other Columns" = Table.Unpivot(#"Filtered Rows1", List.Select(Table.ColumnNames(#"Filtered Rows1"), each Text.Contains(_, "_")), "Attribute", "Value"),

when you merge your headers column you applied "_" as delimiters. The above replacement code is to unpivot any columns containing "_".

Regards

KT

[Anonymous]

Excel sheet Book1 data:

Excel sheet Book 2 data :