Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
Good day!
I would like to seek for your help on this. what function should I use for the table below. I cant explain it thru words but here's what I need to do for my report and the data table as basis:
Generated data table with 10 Rows
Expected outcome:
i dont know if this gonna be possible in Power BI but I hope you'll help me on this.
PS: Disregard why the issue has been gone.
thanks and godbless
Solved! Go to Solution.
Hi @Anonymous
Transform data in power query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc49DoAgDAXguzAzlF9x9AxOhjCY6GCiLt4/scAbJIahQHgfLTEKJWSphcsR8Tpt13Hzbh2JJKPQED+A3PBRo4MpYF7P/cmvQhU2JxC2ERo9HEQX+B5QpoIhJ5ihqPmnp0oCSF+MXWEhVL50mOO/gvP0Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [row = _t, #"Doc Code" = _t, #"Doc Code Sign" = _t, #"Orig Amount" = _t, Department = _t, #"Audited Amt" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"row", Int64.Type}, {"Doc Code", Int64.Type}, {"Doc Code Sign", type text}, {"Orig Amount", Int64.Type}, {"Department", type text}, {"Audited Amt", Int64.Type}}),
#"Removed Duplicates" = Table.Distinct(#"Changed Type", {"Doc Code", "Audited Amt"}),
#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Doc Code"}, {{"sum", each List.Sum([Audited Amt]), type number}, {"all", each _, type table [row=number, Doc Code=number, Doc Code Sign=text, Orig Amount=number, Department=text, Audited Amt=number]}}),
#"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"row", "Doc Code Sign", "Orig Amount", "Department"}, {"all.row", "all.Doc Code Sign", "all.Orig Amount", "all.Department"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded all", each ([all.Doc Code Sign] = "Y"))
in
#"Filtered Rows"
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , what is the source data for that.
Can you share sample data in table format? Or a sample pbix after removing sensitive data.
In addition, may I know what can I use to compare last month data to current month?
I cant use the suggested functions because the DAX cannot accept the MMMM-YYYY format.
The next close idea i can think is to have columns like this:
MONTH --- Amount ---- Amount Last Month -----
(mmmm-yyyy)
1. SEPTEMBER 2019 --- 5000 ---- 0000
2. OCTOBER 2019 ---- 10000 ----- 5000
3. NOVEMBER 2019 ---- 300 ---- 10000
Am I correct? TIA
Hi. Sorry if I wasnt able to include the original data or the generated data.
From this table:
To this outcome:
Hope this can help. TIA 🙂 @amitchandak
Hi @Anonymous
Transform data in power query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc49DoAgDAXguzAzlF9x9AxOhjCY6GCiLt4/scAbJIahQHgfLTEKJWSphcsR8Tpt13Hzbh2JJKPQED+A3PBRo4MpYF7P/cmvQhU2JxC2ERo9HEQX+B5QpoIhJ5ihqPmnp0oCSF+MXWEhVL50mOO/gvP0Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [row = _t, #"Doc Code" = _t, #"Doc Code Sign" = _t, #"Orig Amount" = _t, Department = _t, #"Audited Amt" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"row", Int64.Type}, {"Doc Code", Int64.Type}, {"Doc Code Sign", type text}, {"Orig Amount", Int64.Type}, {"Department", type text}, {"Audited Amt", Int64.Type}}),
#"Removed Duplicates" = Table.Distinct(#"Changed Type", {"Doc Code", "Audited Amt"}),
#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Doc Code"}, {{"sum", each List.Sum([Audited Amt]), type number}, {"all", each _, type table [row=number, Doc Code=number, Doc Code Sign=text, Orig Amount=number, Department=text, Audited Amt=number]}}),
#"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"row", "Doc Code Sign", "Orig Amount", "Department"}, {"all.row", "all.Doc Code Sign", "all.Orig Amount", "all.Department"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded all", each ([all.Doc Code Sign] = "Y"))
in
#"Filtered Rows"
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |