Create a Dynamic Column Based On Other Column Values

[Nikhil_567]

Hi Team,

We have a requirement wherein we need to create a new column using existing 3 columns from the same table.

Input Table-

DataSource	Indication	SKU	DataSource_Mapping	Attributes
A	X	1	A	Indication
A	X	1	A	SKU
B		3	B	SKU
C	Y		C	Indication

Expected Result-

We want this to be dynamically calculated (not statically) using Attributes column values so that Indication & SKU Column values gets populated against the DataSource

DataSource	Indication	SKU	DataSource_Mapping	Attributes	Calculated Column
A	X	1	A	Indication	X
A	X	1	A	SKU	1
B		3	B	SKU	3
C	Y		C	Indication	Y

[Uzi2019]

Hi @Nikhil_567 

Try below dax for  calculated column 

Final = IF(AttributeDS[Attributes]="Indication",AttributeDS[Indication],""&AttributeDS[SKU])

Dont take measure.

I hope I answered your question!

[Nikhil_567]

Thanks @Uzi2019 

We didn't want indication column to be hard-coded. 

[v-yiruan-msft]

@Uzi2019 Thanks for your contribution on this thread.

Hi @Nikhil_567 ,

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

[Nikhil_567]

hi @v-yiruan-msft ,

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.

[v-yiruan-msft · Accepted solution]

Hi @Nikhil_567 ,

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