Forum Discussion
complication with depivotizing
Hi
I have a planning list, that look like the following.
To use it as monthly planning values, i can just depivotize it in power query and use it perfectly.
There is one line per Internal Order. The order is used as key to connect other tables. This is the many side of the relation. So all good.
But now i have received the request to split the internal orders according to an "initiative mapping", That breaks up most of the Internal Orders into several "initiatives".
The spreadsheet now looks like this:
The desired outcome for my PBI report would be like this, example based on Internal Order 1.
I managed to get there with a workaround, i created one 12 months table for each initiative, loaded them into pbi, depivotized and appended them. But i would like to solve this without having to expand my excel by that many columns. Maybe there is a way this could be solved in Power Query, or with DAX?
Thanks a lot for your support.
Best Regards
Fabrizio , Take the first 10 columns in the second one and unpivot the columns one to 8
Remove first 8 and unpivot the month in another table.
merge the two - internal order number from the second table and the unpivoted order number using left join (second to first)
Now you can multiple Percent with number
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
2 Replies
Fabrizio , Take the first 10 columns in the second one and unpivot the columns one to 8
Remove first 8 and unpivot the month in another table.
merge the two - internal order number from the second table and the unpivoted order number using left join (second to first)
Now you can multiple Percent with number
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.amitchandak Thanks for your immediate solution. Works perfectly! Thank you so much for your help.
