• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unpivot in Power Query : Is it possible on more than one related columns ?

Folks,
A question on the Unpivot in Power query. Is it possible to link two or more related columns to Unpivot together ?

Scenario is, I have two columns - say there is a milestone called "Installment 01"; the data I have is in two columns - "Installement 01 Date" (due date) and ""Installement 01 Amount".

I achieved a messy (really messy!) workaround by concatenating the two columns, unpivoting, and then separating the concatenated columns into two. But that is like going around the moon to fly Bangalore to LA !! :p

Any better thoughts ? (Can wait - enjoy your weekend ! :) ) Attached a sample data.

Cheers!
Sudhir
 

Attachments

  • Sample Data.xlsx
    28.4 KB · Views: 5
I'm not clear about this - which is the source data and which the result data? I don't see an issue with concatenating in PQ - it only has to be done once when you build the query.
 
"Due Installment" col A to Col E is the source data. Sheet 1 is result data.
But then, I also realised after post here, that my workaround is actually not working - the unpivot should have added more number of record rows, which is not happening. So back to square one :confused:

because there are numbers involved, not very keen on the concat route...
 
OK - so if you want my help, please provide a workbook with a before and after, mocked up.
 
Don't know if what you want to accomplish is "unpivot", but to answer your query, simply select both columns and then select "unpivot only selected columns".

Would look like this, (I've used your source data without the concatenated column)
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account Name", type text}, {"Customer", type text}, {"Agreement Date", type datetime}, {"Installment 1 Date", type datetime}, {"Installment 1 (₹)", Int64.Type}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Installment 1 Date", "Installment 1 (₹)"}, "Attribute", "Value")
in
    #"Unpivoted Only Selected Columns"
61878
 

Attachments

  • 42285_Sample Data.xlsx
    32 KB · Views: 4
Thanks Guido for trying.

I see while we made some progress, the attribute column is still mixing two different data points - date and amount (attachment for details). I have illustrated what I am trying to achieve ... if it were possible.

Thanks !
Sudhir
 

Attachments

  • 42285_Sample Data (1).xlsx
    34.4 KB · Views: 1
Just reading your comment I went:"Hmm, if you don't want it in the same row, then it should be columns, which is the starting point?"
Viewing your file makes it clear you'd like to have the column title in a separate column, repeated on all rows.

The easiest way I found was simply "unpivot" one column at the time.
 

Attachments

  • Copy of 42285_Sample Data (1).xlsx
    34.2 KB · Views: 5
Back
Top