How to break on row with duplicated columns in two rows

[Netope]

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

[Nathaniel_C]

Hi @Netope ,
You are welcome! 

Thank you,

Nat

[Netope]

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

[Nathaniel_C]

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]
)

Which leads to the same result:

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

[Nathaniel_C]

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

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"

[Nathaniel_C · Accepted solution]

Hi @Netope ,
My Source Table

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

Go to Add Column tab, add conditional column

Remove and rename columns

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

[Netope]

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