Converting a column into an existing row in Power BI

[JenD]

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.

[Anonymous · Accepted solution]

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 · Accepted solution]

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

[Anonymous]

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]

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 · Accepted solution]

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