Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Create a Dynamic Column Based On Other Column Values

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 ...
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

Uzi2019's avatar
Uzi2019
Community Champion
2 years ago

Hi Anonymous 

Try below dax for  calculated column 

 

 

Final = IF(AttributeDS[Attributes]="Indication",AttributeDS[Indication],""&AttributeDS[SKU])
 
Dont take measure.
 
I hope I answered your question!
 
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Thanks Uzi2019 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      hi Anonymous ,

      Clarifying on the requirement below.

      We have a main table with below three columns-

      DataSourceIndicationSKU
      AX1
      B 3
      CY 

       

      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

       

      DataSourceAttribute
      AIndication
      ASKU
      BSKU
      CIndication

       

      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.

       

      DataSourceIndicationSKUCalculatedColumn
      AX1X
      AX11
      B 33
      CY 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.

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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