Power BI : Unpivot Columns

[Anonymous]

Hi, I have my base table like below. 

My Expected Output is like below  :

What I have done so far is that in Direct Query, I have unpivoted the columns which come under 'Component' and unpivoted the columns which come under 'Reverse Component' separately. But naturally it gives me below output.

Every component gets repeated for each reverse component. Can anyone please help me to get the expected output.

[Mariusz · Accepted solution]

Hi @Anonymous 

You can add a Custom Column like below ( added column step ), but I don't think it will work in Direct Query Mode.

What data source are you connecting to as most of the time Unpivot would not work in Direct Query Mode?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1AciIwMjAyDH0MAASsFJUygJUqoUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Loan ID" = _t, #"Due Date" = _t, #"Principal Amt" = _t, #"NI Amount" = _t, #"AI Amount" = _t, #"Principal Reverse Amt" = _t, #"NI Reverse Amt" = _t, #"AI Reverse Amt" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Loan ID", Int64.Type}, {"Due Date", type date}, {"Principal Amt", Int64.Type}, {"NI Amount", Int64.Type}, {"AI Amount", Int64.Type}, {"Principal Reverse Amt", Int64.Type}, {"NI Reverse Amt", Int64.Type}, {"AI Reverse Amt", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
        #table( 
            type table [#"Component"=text, #"Amount"=number, #"Reverse Component"=text, #"Reverse Amount"=number],
            {
                { "Principal Amt", [Principal Amt], "Principal Reverse Amt", [Principal Reverse Amt] },
                { "NI Amount", [NI Amount], "NI Reverse Amt", [NI Reverse Amt] },
                { "AI Amount", [AI Amount], "AI Reverse Amt", [AI Reverse Amt] }
            }
        ), type table
    )
in
    #"Added Custom"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Mariusz · Accepted solution]

Hi @Anonymous 

You can add a Custom Column like below ( added column step ), but I don't think it will work in Direct Query Mode.

What data source are you connecting to as most of the time Unpivot would not work in Direct Query Mode?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1AciIwMjAyDH0MAASsFJUygJUqoUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Loan ID" = _t, #"Due Date" = _t, #"Principal Amt" = _t, #"NI Amount" = _t, #"AI Amount" = _t, #"Principal Reverse Amt" = _t, #"NI Reverse Amt" = _t, #"AI Reverse Amt" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Loan ID", Int64.Type}, {"Due Date", type date}, {"Principal Amt", Int64.Type}, {"NI Amount", Int64.Type}, {"AI Amount", Int64.Type}, {"Principal Reverse Amt", Int64.Type}, {"NI Reverse Amt", Int64.Type}, {"AI Reverse Amt", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
        #table( 
            type table [#"Component"=text, #"Amount"=number, #"Reverse Component"=text, #"Reverse Amount"=number],
            {
                { "Principal Amt", [Principal Amt], "Principal Reverse Amt", [Principal Reverse Amt] },
                { "NI Amount", [NI Amount], "NI Reverse Amt", [NI Reverse Amt] },
                { "AI Amount", [AI Amount], "AI Reverse Amt", [AI Reverse Amt] }
            }
        ), type table
    )
in
    #"Added Custom"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Hi @Mariusz I am connecting to postgresql. Not able to open your PBIX file. It is showing "Object reference not set to an instance of object". Is it because I haven't updated my desktop to the latest release of Power BI ? 

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

I provide a troublesome solution, hope that a better solution:

1.I create a copy of the original query . For  the original query, I tried to unpivot colums for 'Principal Amt','NI Amount' and 'Al Amount' columns and deleted the reverse columns:

 For duplicated query, I unpivot the reverse columns and remove other columns:

1. Add index column for both of them for merging query:

3.Merge query by index column:

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Thank you @v-deddai1-msft ...but will your solution work if I don't have same number of component and reverse component? In my actual case, there are 10 types of components and 9 types of reverse components.

[Mariusz]

Hi @Anonymous 

Yes, the version is probably an issue, but you can just paste the code provided into Blank Query.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

@Greg_Deckler @amitchandak Please help