Get data from dynamic columns without affecting by changes

[jeongkim]

Hi,

Im getting data using 'Expanding Custom.Data' not to specify the column name one by one to reduce errors if its data source's column names change unexpectedly. 

But this one also might have an error if data source's column removed at least one cuz they can find the columnXX in query.

(I think column added would be find).

Is there any way we can get all the existing columns from data source as per their number of column which is dynamic?

#"Weekly Data" = #"TOSS service PO_master"{[Name="Weekly Data"]}[Content],
#"Removed Other Columns" = Table.SelectColumns(#"Weekly Data",{"Content", "Name"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Custom.Hidden] = false) and ([Custom.Kind] = "Sheet")),
#"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"Name", "Custom.Name", "Custom.Data"}),
#"Sorted Rows" = Table.Sort(#"Removed Other Columns1",{{"Name", Order.Descending}}),
#"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Custom", each Text.Middle([Name],23,8)),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", type date}}),
#"Filtered Rows2" = Table.SelectRows(#"Changed Type1", let latest = List.Max(#"Changed Type1"[Custom]) in each [Custom] = latest),
#"Expanded Custom.Data" = Table.ExpandTableColumn(#"Filtered Rows2", "Custom.Data", {"Column1", "Column2", "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"}, {"Custom.Data.Column1", "Custom.Data.Column2", "Custom.Data.Column3", "Custom.Data.Column4", "Custom.Data.Column5", "Custom.Data.Column6", "Custom.Data.Column7", "Custom.Data.Column8", "Custom.Data.Column9", "Custom.Data.Column10", "Custom.Data.Column11", "Custom.Data.Column12", "Custom.Data.Column13", "Custom.Data.Column14", "Custom.Data.Column15", "Custom.Data.Column16", "Custom.Data.Column17", "Custom.Data.Column18", "Custom.Data.Column19", "Custom.Data.Column20", "Custom.Data.Column21", "Custom.Data.Column22", "Custom.Data.Column23", "Custom.Data.Column24", "Custom.Data.Column25", "Custom.Data.Column26", "Custom.Data.Column27", "Custom.Data.Column28", "Custom.Data.Column29", "Custom.Data.Column30", "Custom.Data.Column31", "Custom.Data.Column32", "Custom.Data.Column33", "Custom.Data.Column34", "Custom.Data.Column35", "Custom.Data.Column36", "Custom.Data.Column37", "Custom.Data.Column38", "Custom.Data.Column39", "Custom.Data.Column40", "Custom.Data.Column41", "Custom.Data.Column42"}),

[danextian]

Hi @jeongkim 

Doesn't the data source itself have column names instead of generic ones? If it does. I would add a custom column using Table.PromoteHeaders([Custom.Data], [PromoteAllScalars = true]) so the first rows are treated as the column headers and then expand this custom column instead.

[jeongkim]

Hi,

Your suggestion is actually what I would like to ask next!

Can you see my full code and amend it pls? 

I think the code in red seems unstable since there's possibility column header at particular row number could change in data source.

Will be appreciated if you can read over all my codes and anything can be better please suggest. 

Then I will reply another comment to request and accept solution twice!

let
// Get list of files in SharePoint folder
Source = SharePoint.Contents("xxxxxxxxxxxxxxxxxx.com"),
// Get list of files in SharePoint folder
Documents = Source{[Name="Documents"]}[Content],
// Get list of files in SharePoint folder
#"LGUplus Site Progress" = Documents{[Name="LGUplus Site Progress"]}[Content],
// Get list of files in SharePoint folder
#"TOSS service PO_master" = #"LGUplus Site Progress"{[Name="TOSS service PO_master"]}[Content],
// Get list of files in SharePoint folder
#"Weekly Data" = #"TOSS service PO_master"{[Name="Weekly Data"]}[Content],
#"Removed Other Columns" = Table.SelectColumns(#"Weekly Data",{"Content", "Name"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Custom.Hidden] = false) and ([Custom.Kind] = "Sheet")),
#"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"Name", "Custom.Name", "Custom.Data"}),
#"Sorted Rows" = Table.Sort(#"Removed Other Columns1",{{"Name", Order.Descending}}),
#"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Custom", each Text.Middle([Name],23,8)),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", type date}}),
#"Filtered Rows2" = Table.SelectRows(#"Changed Type1", let latest = List.Max(#"Changed Type1"[Custom]) in each [Custom] = latest),
// Dynamically get column names and expand
ColumnNames = Table.ColumnNames(#"Filtered Rows2"[Custom.Data]{0}),
#"Expanded Custom.Data" = Table.ExpandTableColumn(#"Filtered Rows2", "Custom.Data", ColumnNames),
#"Removed Top Rows" = Table.Skip(#"Expanded Custom.Data",2),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",
{{Table.ColumnNames(#"Promoted Headers"){0}, "Source Name"},
{Table.ColumnNames(#"Promoted Headers"){1}, "Sheet Name"}}),
#"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([Unit price] <> 0)),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Contract signed date", type date}, {"Service#", Int64.Type}, {"Unit price", Int64.Type}, {"Date of list amendment", type date}, {"Total FOC Qty", Int64.Type}, {"Service contract#", Int64.Type}, {"Initial PO amount", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Filtered Rows3" = Table.SelectRows(#"Added Index", each [#"Service#"] <> null and [#"Service#"] <> "")
in
#"Filtered Rows3"

[danextian]

I would like to ask again. Doesn't the data source or the files in SharePoint have column names? It will be problematic to rely on the column order.

[jeongkim]

It does. 

But I would like to have tolerance for unexpected data format changes in the future.

e.g. column added/removed, column header order changed, name changed, moved up/down to other row, etc.

column parellal level is quite improved from other solution of this post below, 

but column with row level still needs to be improved. 

[danextian]

Since it does, you can promote the first row in your Custom.Data to header in a custom column prior to expanding.  The question now is  the row header always in the first row? If not, there should be a column in the source that always contains a value (will never be blank) and will always stay in the same column position.  If both conditions are not met, it will be a big problem - code may work now but not in the future.

[rajendraongole1 · Accepted solution]

Hi @jeongkim -you can use Power Query's Table.ColumnNames function. This function dynamically retrieves all column names from a table, allowing you to expand all existing columns without hardcoding them.

Please check the attached pq code, hope it works