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.
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!
Solved! Go to Solution.
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 |
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 |
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |