Forum Discussion
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 model without the need of additional changes.
The issue is that each month new data comes in. For example, my column names now are 'Sep-19', 'Oct-19', 'Nov-19' while next month it will be 'Oct-19', 'Nov-19', 'Dec-19' and the steps in my query won`t be replicated.
What do you think will be a good way to overcome this?
Thanks in advance!
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
1 Reply
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