Latest Enterprise DNA Initiatives

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

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.

Any help very much appreciated.

BR Martin

1 Like

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

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

Hi @Mjuds,

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

I hope this is helpful

1 Like

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

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