need help

[davidiraz]

hi everyone. i need to develop a dashboard from a sql server and i have a little problem with the data order in one of the table.

1 - i have a table that contain a budget data. the table look like this:

BUDGET NAME	YEAR	JAN	FEB	MARCH	APRIL
ABC123456	2019	1000	1500	2000	3000

and its continue to the next year...

2. i need to create a comparing dashboard between the budget and invoices table that look like this:

INOIVE NUMBER	BUDGET NAME	INVOICE DATE	TOTAL $	CURRENCY
123	ABC123456	01/01/2019	1500	US DOLLAR
456	ABC123456	01/02/2019	2000	US DOLLAR
789	ABC123456	01/03/2029	5000	US DOLLAR

the desired result need to be that the user will be able to choose invoices dates, for example between 01/01/2019

and 31/03/2019

and i need to show somthing like:

INOIVE NUMBER	BUDGET NAME	INVOICE DATE	TOTAL $	BUDGET	DELTA	CURRENCY
123	ABC123456	01/01/2019	1500	1000	-500	US DOLLAR
456	ABC123456	01/02/2019	2000	1500	-500	US DOLLAR
789	ABC123456	01/03/2029	5000	200	-4800	US DOLLAR

how can i do it?

thanks.😀

[vivran22 · Accepted solution]

Hello @davidiraz 

Unpivot option is available in Power Query.

For your budget table, select Budget Name & Year > right click on the and Select Unpivot Other Columns

Select Month & Year column and Merge with a space:

Transform the Month column as Date:

The output:

Hope this helps!

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[davidiraz]

thanks you very much!!!

[Greg_Deckler]

@davidiraz Not sure that I entirely follow, but you will almost certainly want to start by unpivoting your month columns:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjQyNjE1U9JRMjIwtARShgYGBiDKFEwZQXjGICo2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"BUDGET NAME" = _t, YEAR = _t, JAN = _t, FEB = _t, MARCH = _t, APRIL = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"BUDGET NAME", type text}, {"YEAR", Int64.Type}, {"JAN", Int64.Type}, {"FEB", Int64.Type}, {"MARCH", Int64.Type}, {"APRIL", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"BUDGET NAME", "YEAR"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

[davidiraz]

where do i need to put this code?