Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |