Background: I'm trying to create query that incorporates dynamic column names such that if a user changes the name of a column within a table, the logic still follows for any transformations.
The problem is quite straightforward but difficult to explain in just a few sentences. I have tried to break down everything I've done to easily show the problem I am having. If obvious please just skip to the problem at the end.
Demo of Dynamic Columns: To begin making the column Names dynamic, I use:
DynamicNameHeader = Table.ColumnNames(Source)
This creates a list of the column names from the original table. If the Table name is altered in the spreadsheet, this list is dynamically updated. This list can be used to indirectly refer to columns within your M code.
As an example to show this, Here I have changed Column1 in Table1 to Hello and after refreshing the data the outputted table updates the column to read Hello accordingly.
This works, by referring to the DynamicNameHeader list and indexing for the desired column. The code also demonstrates simple transformations that can be achieved in this by changing the text to uppercase and reordering the columns.
M Code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
DynamicHeaderNames =Table.ColumnNames(Source),
#"Uppercased Text" = Table.TransformColumns(#"Source",{{DynamicHeaderNames{0}, Text.Upper, type text}, {DynamicHeaderNames{1}, Text.Upper, type text}}),
#"Reordered Columns" = Table.ReorderColumns(#"Uppercased Text",{DynamicHeaderNames{1}, DynamicHeaderNames{0}})
in
#"Reordered Columns"
This is just an easy example to show how I'm trying to integrate Dynamic Columns in this way.
PROBLEM Here is a simple version of the actual data that has been sorted in the outputted table such that the data in Column 1 is Prioritised over values in Column2. The numbers in column 3 just correspond to values in column2.
Working M code to achieve this output:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
//combine the columns
#"Added Custom" = Table.AddColumn(Source, "Custom", each
let
L1 = if [Column2] = "-" then {[Column1]}
else List.Combine({{[Column1]},Text.Split([Column2],"#(lf)")}),
L2 = if [Column2] = "-" then {[Column3]}
else List.Combine({{"-"},Text.Split([Column3],"#(lf)")})
in
List.Zip({L1,L2})),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Column1", "Column2", "Column3"}),
//split the combined columns
#"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns1", "Custom"),
#"Extracted Values" = Table.TransformColumns(#"Expanded Custom",
{"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values",
"Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Column1", "Column3"})
in
#"Split Column by Delimiter"
I wish to make this code Dynamic as in the simplified example, however I am running into lots of syntax issues when I add the Custom Column step.
In essence I wish to reference columns 1, 2 and 3 indirectly by indexing the DynamicNameHeader list as before. Is this possible? Importantly, this isn't just to allow the column names to be altered by the user, but so that transformations to the data also can refer to the relevant columns dynamically too. This Custom Column Transformation is pretty much the only step proving difficult because it uses [] which dont appear to be compatible with DynamicNameHeader{x}.
I hope this explanation is clear enough to understand what I am trying to achieve and if anyone has any solutions to this problem it would be really appreciated.
