Pivot, unpivot and shaping doubts

[areymejias]

Hello everyone,

I do have a database like this:

And I would like to shape it into this:

After 10000 million fail tries of pivot, unpivot and shape. I am in your hands!

Thanks in advance,

Angel

[Anonymous · Accepted solution]

Hi @areymejias ,

You can take a look at the following formula if it was suitable for your requirements.

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYX1DpVgdCN8IzDeC843BfGMwPwlNfRKa+iRk9bEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Type", Int64.Type}, {"Date", type date}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"ID"}, {{"Count", each Table.PromoteHeaders(Table.Transpose(Table.RemoveColumns(_,{"ID"}))),type table [ID=text, Attribute=text, Value=anynonnull]}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", Table.ColumnNames(#"Grouped Rows"[Count]{0}), Table.ColumnNames(#"Grouped Rows"[Count]{0}))
in
    #"Expanded Count"

Original table:

Convert and expanded:

Regards,

Xiaoxin Sheng

[Greg_Deckler]

Not sure of the reason you want to do that, is that only for display purposes or is that how you want the data structured in the table? Because the way you have it is probably the best format overall. But, if you really want it modified to your second example, @ImkeF  can probably assist.