PIVOT - UNPIVOT Rows

[sabedin]

Hello,

Please refer to the attached screenshot. I am not sure how to best prepare this data for creating Visuals. I guess I have to create on column State with the data from the first Row, and one other column L-O with the data from the second Row. I tried different transformation steps but no luck.

Any ideas please

Thank you

Sabedin

[ImkeF · Accepted solution]

You should find sth on this blog about doing this with the UI.

But I prefer a formula-solution (for performance-reasons, especially if your data is large):

let
// Replace this with your source-data
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRcgRiJyB2VorViVZySSxJBXJ8EvNSEJRzfl4JWNbAUA+IjAwMzYGihkBsBMTGECkjZCkDkByIMDaAyBqjyEKkIfJABbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Spalte1 = _t, Spalte2 = _t, Spalte3 = _t, Spalte4 = _t]),
    Rename = Table.RenameColumns(Source, List.Transform(Table.ColumnNames(Source), each {_, Text.Combine({Record.Field(Source{0}, _), Record.Field(Source{1}, _)}, "--")})),
    RemoveTopTwo = Table.Skip(Rename,2),
// Depending on how the blanks in your first fields come in, you might need to chang {"--DATE"} to {"DATE"}
    UnpivotOthers = Table.UnpivotOtherColumns(RemoveTopTwo, {"--DATE"}, "Attribute", "Value"),
    Split = Table.SplitColumn(UnpivotOthers,"Attribute",Splitter.SplitTextByDelimiter("--", QuoteStyle.Csv),{"Country", "Category"})
in
    Split