How to pivot and display multiple values

[joxe]

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.

[Anonymous]

hi there, in my case, it is a little different, how can i do this? 

Because after pivoting, it is showing me this: 

i want to maintain all the values as it is. and then clean it up somemore. thanks. appreciate any help. thanks.

[Anonymous]

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.

 

 

[ImkeF · Accepted solution]

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?:

[joxe]

I am very grateful to everyone for your solutions. I have used ImkeF's because it seemed the easiest to implement, and it has been great for me. Many thanks.

[Anonymous]

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

[HotChilli]

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

[ImkeF]

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"