Shaping my excel datasource to a table form

[Anonymous]

Hey Everyone ,

I have this below report , that is for two different categories. They have some historical data ( total of years) and this years individual months. How can I change the data format to make it usable in power bi

Thanks

[v-juanli-msft]

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

[v-juanli-msft]

Hi @Anonymous

Is the screenshot the expected result matrix you want finally?

What does your data source table structure?

Does historical data ( total of years) and this years individual months list in one column or two seperate columns?

Is it like this?

category	Item	Unit	Actuals	date	year	data_history	data_this year
C - MOE	1	1	Actuals	1/1/2013	2013
C - MOE	2	1	Actuals	1/1/2013	2013
C - MOE	3	1	Actuals	1/1/2013	2013
C - MOE	4	1	Actuals	1/1/2013	2013
C - MOE	5	1	Actuals	1/1/2013	2013

Best Regards

Maggie

[Anonymous · Accepted solution]

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

[Anonymous]

Here is the data sample

https://www.dropbox.com/s/cuu2ntuxvus4t3w/delete.xlsx?dl=0

[AlB]

Hi @Anonymous

Have you tried the Pivot/Unpivot feature on the Query Editor?

[Anonymous]

Didnt work unfornately

[AlB]

@Anonymous

If you share a sample of the table maybe someone can help

[Anonymous]

Yep, can definitely be done in Power Query.  Not easy but doable for sure.  Just add some data here