Forum Discussion
How to pivot and display multiple values
Hello,
I can't solve a problem that seems too easy. If someone could help me. We have a program that gives us this type of list:
We need to put it this way, but I can't find a way to transform it with Query:
The problem is that when I pivot the first column of the original table, Query only shows me one value per row or it gives me an error. It could also be this other way, but I can't get it either:
I would appreciate your help. Thank you.
Hi Anonymous ,
I am always up for improved solutions, but in this case I'm wondering why.
My pivot solution isn't only faster to implement but will also run faster when executed. Or am I missing something here?:
7 Replies
Hi joxe ,
pivoting is still the way to go, but you have to adjust the aggregation function for the values:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PS0msVNJRMrC0MjAA0r6JRZVKsTpYZLzyM/LAMiGlqcUQKUMDiJRjHlAmFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Day = _t, Hour = _t, Name = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Day", type text}, {"Hour", type time}, {"Name", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Day]), "Day", "Name", each Text.Combine(_, "#(lf)"))
in
#"Pivoted Column"Does this have to be in Power Query. The original format is very good for storage and further analysis.
If you keep it this way, create a matrix with Hour and Day then write a measure like:
MeasureW = CONCATENATEX(theTable, theTable[Name], ",")it should work
I put this gif together to walk you through the steps to get to this result. I started with a group by. I knew tables could be converted to lists and then concatenated but couldn't remember how so I found some good info here (See reply by Marc Pincince ) to parse the grouped names into a concatenated list. From there it was a series of pivot/unpivots and cleanup.
Hi Anonymous ,
I am always up for improved solutions, but in this case I'm wondering why.
My pivot solution isn't only faster to implement but will also run faster when executed. Or am I missing something here?:Wasn't trying to say your solution isn't better. There is usually more than one way, I just shared the way I came up with. If yours is faster and better then the joxe should go with it. Doesn't hurt my feelings
