Forum Discussion

JenD's avatar
JenD
New Member
2 years ago
Solved

Converting a column into an existing row in Power BI

Hoping someone can help me with a solution to below.

In my data table, I have Stakeholders and their associated Stakeholder Names.  "Opp Owner" which is currently it's own column should be a Stakeholder type.  So I would like to add the Opp Owner to the type of Stakeholders and reflect the Opp Owner Name in the Stakeholder Name column.  I hope this makes sense.  Any help you can provide would be appreciated.  Thanks in advance for your guidance.

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi JenD ,

    Please try:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc/BCsIwDADQf+m5h80/2OZFXPEmg7FDbKMWSgpNKuzvbQ96WU8hyUtC1lX1XdcrrWbICUocrI2ZxADBC1MpjJAeUFqbPthlvk2ROAcBkpJf8eOpBQdmz1VMb3A/cPpPaGW8TZHjU8azqTcx7OSgJS8kmJAFXS1C2kNLHb64eyrrti8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Case ID" = _t, #"Opp Owner Name" = _t, Stakeholders = _t, #"Stakeholder Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Opp Owner Name", type text}, {"Stakeholders", type text}, {"Stakeholder Name", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Case ID", "Stakeholders", "Stakeholder Name"}, "Attribute", "Value"),
    Custom1 = Table.RenameColumns(Table.Distinct(Table.SelectColumns(#"Unpivoted Columns",{"Case ID", "Attribute", "Value"})),{{"Attribute","Stakeholders"},{"Value","Stakeholder Name"}}),
    Custom2 = Table.RemoveColumns(#"Changed Type",{"Opp Owner Name"}),
    Custom3 = Table.Combine({Custom1, Custom2})
in
    Custom3

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    Try this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY/LCsIwEEX/pesKfSyCyz421RYXggili7EdTaBMSpIK/XsTUBHbiquQO+cOc+raKzYhi8LQ870SRgX2TdpWjmQqILihskEK6gJ21PhL+Lk8ZJL02BsgY/97vAtaYROthXZQxqF7MlEcsfjdm+/LuLIdOXB7y3JjdvBJEKywlWiV1PJq0rxyathP1K3BBRlUqA12LgQ19S+QBcHWZkeDAwcS+Om2QyDCH+Qfhkv7vy1zcJbNAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Case ID" = _t, #"Opp Owner Name" = _t, Stakeholders = _t, #"Stakeholder Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Case ID", type text}, {"Opp Owner Name", type text}, {"Stakeholders", type text}, {"Stakeholder Name", type text}}),
    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Case ID"}, {
        {"New", (t)=> Table.FromColumns(
                {{"Opp Owner"} & t[Stakeholders],
                 {t{0}[Opp Owner Name]} & t[Stakeholder Name]},
                 {"Stakeholders", "Stakeholder Name"}),type table[Stakeholders=text, Stakeholder Name=text]}
            }),
    
    #"Expanded New" = Table.ExpandTableColumn(#"Grouped Rows", "New", {"Stakeholders", "Stakeholder Name"}, {"Stakeholders", "Stakeholder Name"})
in
    #"Expanded New"

     

    Original

    Results

     

     

     

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi JenD ,

    Is this your expected output?


    advanced editor:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc/BCsIwDADQf+m5h80/2OZFXPEmg7FDbKMWSgpNKuzvbQ96WU8hyUtC1lX1XdcrrWbICUocrI2ZxADBC1MpjJAeUFqbPthlvk2ROAcBkpJf8eOpBQdmz1VMb3A/cPpPaGW8TZHjU8azqTcx7OSgJS8kmJAFXS1C2kNLHb64eyrrti8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Case ID" = _t, #"Opp Owner Name" = _t, Stakeholders = _t, #"Stakeholder Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Opp Owner Name", type text}, {"Stakeholders", type text}, {"Stakeholder Name", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Stakeholders]), "Stakeholders", "Opp Owner Name")
in
    #"Pivoted Column"

     If I have misunderstood your question, please feel free to contact me.

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

    • JenD's avatar
      JenD
      New Member
      2 years ago

      Thank you Gao, 

      No, this is not my expected outcome.  I would like a row to be added for each case to reflect the Opp Owner under Stakeholders.  The Opp Owner is currently in its own column today.  Does this make sense?  Thanks in advance, Jen

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi JenD ,

        Please try:

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc/BCsIwDADQf+m5h80/2OZFXPEmg7FDbKMWSgpNKuzvbQ96WU8hyUtC1lX1XdcrrWbICUocrI2ZxADBC1MpjJAeUFqbPthlvk2ROAcBkpJf8eOpBQdmz1VMb3A/cPpPaGW8TZHjU8azqTcx7OSgJS8kmJAFXS1C2kNLHb64eyrrti8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Case ID" = _t, #"Opp Owner Name" = _t, Stakeholders = _t, #"Stakeholder Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Opp Owner Name", type text}, {"Stakeholders", type text}, {"Stakeholder Name", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Case ID", "Stakeholders", "Stakeholder Name"}, "Attribute", "Value"),
    Custom1 = Table.RenameColumns(Table.Distinct(Table.SelectColumns(#"Unpivoted Columns",{"Case ID", "Attribute", "Value"})),{{"Attribute","Stakeholders"},{"Value","Stakeholder Name"}}),
    Custom2 = Table.RemoveColumns(#"Changed Type",{"Opp Owner Name"}),
    Custom3 = Table.Combine({Custom1, Custom2})
in
    Custom3

        Best Regards,
        Gao
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
        If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

        How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

  • ronrsnfld's avatar
    ronrsnfld
    Icon for Super User rankSuper User
    2 years ago

    Try this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY/LCsIwEEX/pesKfSyCyz421RYXggili7EdTaBMSpIK/XsTUBHbiquQO+cOc+raKzYhi8LQ870SRgX2TdpWjmQqILihskEK6gJ21PhL+Lk8ZJL02BsgY/97vAtaYROthXZQxqF7MlEcsfjdm+/LuLIdOXB7y3JjdvBJEKywlWiV1PJq0rxyathP1K3BBRlUqA12LgQ19S+QBcHWZkeDAwcS+Om2QyDCH+Qfhkv7vy1zcJbNAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Case ID" = _t, #"Opp Owner Name" = _t, Stakeholders = _t, #"Stakeholder Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Case ID", type text}, {"Opp Owner Name", type text}, {"Stakeholders", type text}, {"Stakeholder Name", type text}}),
    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Case ID"}, {
        {"New", (t)=> Table.FromColumns(
                {{"Opp Owner"} & t[Stakeholders],
                 {t{0}[Opp Owner Name]} & t[Stakeholder Name]},
                 {"Stakeholders", "Stakeholder Name"}),type table[Stakeholders=text, Stakeholder Name=text]}
            }),
    
    #"Expanded New" = Table.ExpandTableColumn(#"Grouped Rows", "New", {"Stakeholders", "Stakeholder Name"}, {"Stakeholders", "Stakeholder Name"})
in
    #"Expanded New"

     

    Original

    Results