Pivot Problem (similar, but also different to Paul Boyes' one)

Power Query
1

Hello.

I received a quite weird excel sheet, that I managed to import into PQ (with a lot of twists, but finally…)

Nevertheless the current format the data is in, is not usable for any reporting.
And, same as Paul Boyes, I already tried a lot of combinations to get to my desired format, but until now failed to succeed.

Here’s the data (current and target format).
I think it’s (hopefully) self-explaining.

image
image1010×610 15.9 KB

Any help very much appreciated.

BR Martin

1 Like
2

Hi @Mjuds,

Please always provide a sample in xlsx so members can better assist you.

With the first 2 columns selected an unpivot other columns should do the trick, paste this M code in a new blank query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtIBId/MnJzUIiAjODezJANIO6Yk5hYDaafEbKB4rE60kpGJroGxrpEhUDA3vygvMy8dyCrPL8oGUnn5CjhZaHoT00pSi/Ly8/NQFKNScK2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    PromoteHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    UnpivotOtherColumns = Table.UnpivotOtherColumns(PromoteHeaders, {"", "_1"}, "Attribute", "Value"),
    RenameColumns = Table.RenameColumns(UnpivotOtherColumns,{{"", "Date"}, {"_1", "Daytime"}, {"Attribute", "Name"}, {"Value", "Status"}})
in
    RenameColumns

I hope this is helpful

1 Like
3

Hi Melissa.

Thanks for providing that M code.

I have provided an Excel-file now (sorry for not doing it in the first place).

As said, this is only sample data.
The real dataset consists of much more date entries and also there are more than 50 names.

Since your solution is customized to my sample date, my question is if there is a more generic solution, which I can adopt to my real data.

PQ Sample Data SW.xlsx (14.3 KB)

Best regards
Martin

1 Like
4

Hi @Mjuds,

So maybe a short video will be more helpful.
Follow these steps and you are good to go.

Unpivot Other Columns
Unpivot Other Columns1920×1048 919 KB

I hope this is helpful

1 Like
5

Hi @Melissa.

Indeed, this is VERY helpful, works perfectly (it was the best idea to use a video to show it​:+1::+1:).

The thing I forgot (I was not aware of and still don’t fully understand) was to Promote Headers.

If you wouldn’t mind could you clarify this point a bit?

And again, thanks a lot, Melissa.
BR Martin

1 Like
6

Hi @Mjuds,

Glad to hear it and not a problem at all.
If you watch closely you’ll notice that the names in the headers move to the “Attribute” column when pivoted - so if the names are already in the header you can skip that part else you have to Promote Headers first.

1 Like