Forum Discussion
Create a Dynamic Column Based On Other Column Values
Hi Anonymous ,
Please apply the below codes for the main table in Power Query Editor and check if it can return your expected result...
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYoAYkOlWJ1oJScgC4iMwRxnICsSJBAbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DataSource = _t, Indication = _t, SKU = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"DataSource", type text}, {"Indication", type text}, {"SKU", Int64.Type}}), #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Indication", "SKU"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Only Selected Columns", each ([Value] <> "")) in #"Filtered Rows"Best Regards
Thanks Uzi2019
We didn't want indication column to be hard-coded.
Uzi2019 Thanks for your contribution on this thread.
Hi Anonymous ,
The formula supplied by Uzi2019 was intended to facilitate the attainment of the expected outcome, however, it appears that it has not met the objective. Could you please provide additional clarification on the below sentence? It would be greatly appreciated if you could supplement your explanation with more sample data and expound on the underlying logic.
We didn't want indication column to be hard-coded.
Best Regards
hi Anonymous ,
Clarifying on the requirement below.
We have a main table with below three columns-
DataSource Indication SKU A X 1 B 3 C Y Now we have got a mapping table, in which Attribite columns consists of column names from main table (shared above) which we need to map against data source
DataSource Attribute A Indication A SKU B SKU C Indication Expected Result-
We are trying to calculate a new column in the main table, where we are using Attribute column from the mapping table to get the correspondig values in the new column dynamically against the respective DataSource from the column names mentioned in the Attribute column of mapping table.
DataSource Indication SKU CalculatedColumn A X 1 X A X 1 1 B 3 3 C Y Y I hope I was able to explain you the situation.
Let me know incase any further clarification is required.
Greg_Deckler Ritaf1983 Can you please take a look and help me out here.
Hi Anonymous ,
Please apply the below codes for the main table in Power Query Editor and check if it can return your expected result...
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYoAYkOlWJ1oJScgC4iMwRxnICsSJBAbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DataSource = _t, Indication = _t, SKU = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"DataSource", type text}, {"Indication", type text}, {"SKU", Int64.Type}}), #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Indication", "SKU"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Only Selected Columns", each ([Value] <> "")) in #"Filtered Rows"Best Regards