Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.😀
Solved! Go to 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
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
thanks you very much!!!
@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"
where do i need to put this code?
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |