Forum Discussion
Solved
Group Month Columns
Hi guys, I`m working with a Sharepoint .csv file as a data source to build PowerBI dashboards. The file will be updated on a monthly basis and I would like to be able to use the existing data mod...
Hi pbirookie
I think the best way is Unpivot Column.
Smth.like this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElV0lEyBONYnWgl/6LEvHSQkBEYg4RC8ksSc4A8YzCOjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Period = _t, #"09-2019" = _t, #"10-2019" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", type text}, {"09-2019", Int64.Type}, {"10-2019", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Period"}, "Attribute", "Value") in #"Unpivoted Other Columns"as result you will get comfortable table
PeriodAttributeValue
Apple 09-2019 1 Apple 10-2019 1 Orange 09-2019 2 Orange 10-2019 2 Total 09-2019 3 Total 10-2019 3
az38
Community Champion
Community ChampionHi pbirookie
I think the best way is Unpivot Column.
Smth.like this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElV0lEyBONYnWgl/6LEvHSQkBEYg4RC8ksSc4A8YzCOjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Period = _t, #"09-2019" = _t, #"10-2019" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", type text}, {"09-2019", Int64.Type}, {"10-2019", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Period"}, "Attribute", "Value")
in
#"Unpivoted Other Columns"as result you will get comfortable table
PeriodAttributeValue
| Apple | 09-2019 | 1 |
| Apple | 10-2019 | 1 |
| Orange | 09-2019 | 2 |
| Orange | 10-2019 | 2 |
| Total | 09-2019 | 3 |
| Total | 10-2019 | 3 |