Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
The issue is a bit complicated, so I'll illustrate with a table.
Basically, I want to create the "times" column in Power Query.
I've tried googling it but the solutions were mostly in Dax, instead of M which is used by Power Query.
I want to calculate how many times a country has participated over the years.
Thank you
Solved! Go to Solution.
Hi @anbeegod ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can achieve it in Power Query Editor, copy the below applied codes in your Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNFDSUXLOyMxLBNKRqcVKsTpgYUPswkZIwjAxYyxiJljEQDa5pxblJuZVYtqFVcIIl4QxigSypZiiprgMAbknNNgR0y0YgkbYBI2xCZrABZGtx1Bmhk3QHElvLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Country = _t, Participated = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Country", type text}, {"Participated", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Participated] = "Yes" then 1 else null),
#"Grouped Rows" = Table.Group(#"Added Conditional Column", {"Country"}, {{"Times", each List.Sum([Custom]), type nullable number}, {"Details", each _, type table [Year=nullable number, Country=nullable text, Participated=nullable text, Custom=nullable number]}}),
#"Expanded Details" = Table.ExpandTableColumn(#"Grouped Rows", "Details", {"Year", "Participated"}, {"Year", "Participated"})
in
#"Expanded Details"
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Hi @anbeegod ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can achieve it in Power Query Editor, copy the below applied codes in your Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNFDSUXLOyMxLBNKRqcVKsTpgYUPswkZIwjAxYyxiJljEQDa5pxblJuZVYtqFVcIIl4QxigSypZiiprgMAbknNNgR0y0YgkbYBI2xCZrABZGtx1Bmhk3QHElvLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Country = _t, Participated = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Country", type text}, {"Participated", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Participated] = "Yes" then 1 else null),
#"Grouped Rows" = Table.Group(#"Added Conditional Column", {"Country"}, {{"Times", each List.Sum([Custom]), type nullable number}, {"Details", each _, type table [Year=nullable number, Country=nullable text, Participated=nullable text, Custom=nullable number]}}),
#"Expanded Details" = Table.ExpandTableColumn(#"Grouped Rows", "Details", {"Year", "Participated"}, {"Year", "Participated"})
in
#"Expanded Details"
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
@anbeegod , In power query you have to duplicate the table, Filter, yes, and then group by using country and then join it back with your table using merge
User | Count |
---|---|
86 | |
82 | |
68 | |
67 | |
55 |
User | Count |
---|---|
123 | |
100 | |
90 | |
83 | |
66 |