cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pbirookie
Frequent Visitor

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!

 

Capture.JPG

 

1 ACCEPTED SOLUTION
az38
Super User II
Super User II

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

Apple09-20191
Apple10-20191
Orange09-20192
Orange10-20192
Total09-20193
Total10-20193

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

1 REPLY 1
az38
Super User II
Super User II

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

Apple09-20191
Apple10-20191
Orange09-20192
Orange10-20192
Total09-20193
Total10-20193

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors