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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Netope
Frequent Visitor

How to break on row with duplicated columns in two rows

Dear community:

 

Each record in our table has a field for CAPEX expenses and one field for OPEX expenses, so we had to add two calculated columns to set the expense type (CAPEX Exp Type, and OPEX Exp Type in this example). Now we need to massively upload this data into a system but the format it uses is different, it needs the cost and therefore the expense type to be split in different rows. 

                                                                                          

This picture shows what we need to achieve.

 

Any help would be very much appreciated

 

Netope_1-1689874343380.png

 

 

1 ACCEPTED SOLUTION

Hi @Netope ,
My Source Table

Nathaniel_C_1-1689876434894.png

Select the two Type columns, go to Transform tab, Unpivot columns.

Nathaniel_C_2-1689876730929.png

Go to Add Column tab, add conditional column

Nathaniel_C_3-1689876901247.png

Remove and rename columns

Nathaniel_C_4-1689876988862.png


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
Nathaniel_C
Super User
Super User

Hi @Netope ,
You are welcome! 

Thank you,

Nat





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Netope
Frequent Visitor

Thanks Nathaniel, this exactly what I was looking for. Very much appreciate you help

Hi @Netope ,
This made me interested as to whether we could do this in DAX, and this is what I came up with for you.

 

//In Power BI, build a NEW Table

Unpivot by Dax = 

Var _Table1 = SELECTCOLUMNS('Expense (2)',"ID",[ID],"Descrption",[Description],"CAPEX",[CAPEX],"OPEX",[OPEX],"Attribute","CAPEX TYPE" ,"Values", [CAPEX Type])
Var _Table2 = SELECTCOLUMNS('Expense (2)',"ID",[ID],"Descrption",[Description],"CAPEX",[CAPEX],"OPEX",[OPEX],"Attribute","OPEX TYPE" ,"Values", [OPEX Type])
var _join = UNION(_Table1,_Table2)

return _join

 

And adding a calculated column:

 

Cost = SWITCH(TRUE(),

[Attribute]="CAPEX TYPE",[CAPEX],
[Attribute] = "OPEX TYPE",[OPEX]
)

 

Nathaniel_C_0-1689890518198.png

Which leads to the same result:

Nathaniel_C_1-1689890617963.png


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel







Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Nathaniel_C
Super User
Super User

Hi @Netope ,
If I understand your issue, this is what it should look like:

Nathaniel_C_0-1689876185190.png

Below is the code to build the table Expense in Power Query.  You may want to paste this into the advanced editor.

I will follow this with the steps.

 


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpMSgaSRgYGBhAKSDonFmSWJOYAWT6JSflFSrE60UpGQF5KahqQNIQoRVXo56gUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Description = _t, CAPEX = _t, OPEX = _t, #"CAPEX Type" = _t, #"OPEX Type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Description", type text}, {"CAPEX", Int64.Type}, {"OPEX", Int64.Type}, {"CAPEX Type", type text}, {"OPEX Type", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "Description", "CAPEX", "OPEX"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if [Attribute] = "CAPEX Type" then [CAPEX] else if [Attribute] = "OPEX Type" then [OPEX] else null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Custom", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"CAPEX", "OPEX", "Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Expense Type"}, {"Custom", "Cost"}})
in
    #"Renamed Columns"

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @Netope ,
My Source Table

Nathaniel_C_1-1689876434894.png

Select the two Type columns, go to Transform tab, Unpivot columns.

Nathaniel_C_2-1689876730929.png

Go to Add Column tab, add conditional column

Nathaniel_C_3-1689876901247.png

Remove and rename columns

Nathaniel_C_4-1689876988862.png


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi Nathaniel, thanks for your quick reply! Let me give a try

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors