Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
JenD
New Member

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.

 

JenD_0-1717453744813.png

 

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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

vcgaomsft_0-1717636428092.png

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

View solution in original post

ronrsnfld
Super User
Super User

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

ronrsnfld_0-1717641285240.png

Results

ronrsnfld_2-1717641355016.png

 

 

 

 

 

View solution in original post

4 REPLIES 4
ronrsnfld
Super User
Super User

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

ronrsnfld_0-1717641285240.png

Results

ronrsnfld_2-1717641355016.png

 

 

 

 

 

Anonymous
Not applicable

Hi @JenD ,

Is this your expected output?

vcgaomsft_0-1717465320998.png
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

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

 

JenD_1-1717619140777.png

 

 

Anonymous
Not applicable

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

vcgaomsft_0-1717636428092.png

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.