Forum Discussion
Shaping my excel datasource to a table form
Attached below is the PBIX file, please take a look at the Applied Steps in Power Query. Didnt want to tie this to a file (since you woulnt be able to access) so just copied and pasted your data. But you would want to tie this to a file, or even better yet a folder if that is possible. Here's the final output in Power Query that you can load and then do all your DAX and such:
PBIX file:
https://1drv.ms/f/s!AoQIGRpzoxRH8kvki-UKr7N0uy3f
Hi Anonymous
If your link and screenshot shows the data format in excel, as tested, Nick_M's solution and pbix can work for your problem.
In his pbix, the final step is to filter the rows which are current year's data.
If you want a table with both categories "C - MOE" and "C - M" and both historical and current year's data,
you could only filter the rows which "item" column doesn't equal to null in each table.
also add a custom column in two tables named "category"
Finally, append two tables as new.
If you want a table with both categories "C - MOE" and "C - M" and only historical data,
a table with both categories "C - MOE" and "C - M" and only current year's data,
please create a blank query, open the Advanced editor, paste the code
"History data"
let
Source = Append1,
#"Filtered Rows" = Table.SelectRows(Source, each ([Month] = "Total"))
in
#"Filtered Rows"
"Current year data"
let
Source = Append1,
#"Filtered Rows" = Table.SelectRows(Source, each [Month] <> "Total")
in
#"Filtered Rows"
Finally, in Power BI, you can get the same visual as in excel, also you can create more visual with the table.
Best Regards
Maggie