Require help with data re-organisation, unpivot columns options with a slight twist.

[MKY]

I have data in somthing like this, upto Dec in each category. every month a fresh report will come for about 1500 items under category. Using unpivot for the data columns I just feel the no of columns go up significantly. 

		Value	Qty	Value	Qty
category	abc2024	Jan 24	Feb 24	Mar 24	Jan 24	Feb 24	Mar 24	Jan 25	Feb 25	Mar 25	Jan 25	Feb 25	Mar 25
apple		1	2	3	4	5	6	7	8	9	10	11	12
orange		1	2	3	4	5	6	7	8	9	10	11	12
grapes		1	2	3	4	5	6	7	8	9	10	11	12

I'm think if I can organise data in the following format can help me reduce no. of rows as the category I'm handling is about 1500 and the report will be published monthly so data will keep on adding. I'm open to any other suggestions on managing such data.

category	Reporting Month	Month	Value 2024	Value 2025	Qty 2024	Qty 2025
apple	Jan 24	Jan	1	7	4	10
apple	Jan 24	Feb	2	8	5	11
apple	Jan 24	Mar	3	9	6	12
Orange	Jan 24	Jan	1	7	4	10
Orange	Jan 24	feb	2	8	5	11
Orange	Jan 24	mar	3	9	6	12
grapes	Jan 24	Jan	1	7	4	10
grapes	Jan 24	feb	2	8	5	11
grapes	Jan 24	mar	3	9	6	12
apple	Feb 24	Jan
apple	Feb 24	Feb
apple	Feb 24	Mar
Orange	Feb 24	Jan
Orange	Feb 24	Feb
Orange	Feb 24	Mar
grapes	Feb 24	Jan
grapes	Feb 24	Feb
grapes	Feb 24	Mar

appreciate any thoughts or insights and options to explore, somthing I don't need to carry many operations each month and be more sustainable byitself except adding fresh data each month.

[Anonymous · Accepted solution]

Hi @MKY 

Based on your description , I create the following code and you can refer to(Note: In your original table, you don't have the report month field, so I don't create it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyElV0lFSAGJDIDYCYmMgNgFiUyA2A2JzILYAYkuQGgMQAVJpaKQUqxOtlF+UmJdOkQnpRYkFqcVkmxALAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"category " = _t, abc2024 = _t, #"Jan 24" = _t, #"Feb 24" = _t, #"Mar 24" = _t, #"Jan 24.1" = _t, #"Feb 24.1" = _t, #"Mar 24.1" = _t, #"Jan 25" = _t, #"Feb 25" = _t, #"Mar 25" = _t, #"Jan 25.1" = _t, #"Feb 25.1" = _t, #"Mar 25.1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Jan 24", Int64.Type}, {"Feb 24", Int64.Type}, {"Mar 24", Int64.Type}, {"Jan 24.1", Int64.Type}, {"Feb 24.1", Int64.Type}, {"Mar 24.1", Int64.Type}, {"Jan 25", Int64.Type}, {"Feb 25", Int64.Type}, {"Mar 25", Int64.Type}, {"Jan 25.1", Int64.Type}, {"Feb 25.1", Int64.Type}, {"Mar 25.1", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"abc2024"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"category "}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter",each [Attribute.2],each if Text.Contains([Attribute.2],"." )then "Qty"&" "&[Attribute.2] else "Value"&" "&[Attribute.2],Replacer.ReplaceValue,{"Attribute.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Replaced Value", "Attribute.2", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, false), {"Attribute.2.1", "Attribute.2.2"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Attribute.2.2"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Attribute.2.1]), "Attribute.2.1", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute.1", "Month"}})
in
    #"Renamed Columns"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MKY]

Ho Yolo, this is impressive. Thanks a lot. I'm planning to start with unpivot option to check if it can resolve, else what you have shared is what I was looking for. I might contact you again when I further do the deep dive into this option.

[RossEdwards]

In my opinion, your original method is correct.  Unpivot the data and let Power BI deal with having lots of rows.  Aggregating data is what Power BI is good at. It can handle millions of rows without a sweat.  Just make sure your model is a star schema and you have the appropriate dimensional tables to get the best performance.

You should be able to let power query handle your dynamically changing data and unpivot without issues.  The new method you seem to be talking about seems like it will create unncessary admin overhead.

[MKY]

Hi Ross, Thanks a lot, I also felt that to be a preferred option. couple of linked doubts I am not sure on how to handle were:

1. How best to label each column, such that charts can easily be prepared mainly on month basis. would there be issues if I use Value_Jan2024, will the system be still able to consider as time? can I still calculate YTD or cummulative data month on month? ( I could use addition function as a work around but not sure in built YTD functions can be used)
2. Since each month I get a fresh report that includes data for the 12 months (YTD+YTG). each month expected to compare current changes compared to last month.
3. analyse YTD & YTG against annual plan etc.

So any suggestions on how to label columns that would make life easier would be great.

[RossEdwards]

Assuming you have a reliable consistency in your values, after unpivoting i'd create a "date" column and use the function Date.FromText.  This date can be linked to a date table for time intelligent reporting.

[MKY]

Hi Ross, unfortunately the data doesn't has a date field in any column. I was thinking of including a month field column when the report is created (monthly) like Jan_IBP2024, Feb_IBP2024 and so on....Would you suggest that I rather use 'Jan2024'? Is there a way to use this field to calculate YTD and YTG. For example a report created for Feb2024 has actual of Jan & Feb (YTD) and Forecast for remaining 10 months (YTG). I would like to be able to use and present data each month as spliet of YTD & YTG. each month's report an additional actual month's data will be included.