Power BI : Unpivot Columns

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"

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