Group Month Columns

[pbirookie]

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!

[az38 · Accepted 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