Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have a data like this in power query,
CALL NO. | Sheet1.Count (OUTPUT AFTER GROUOPING AND INDEXING |
ABC | 1 |
PQR | 1 |
ERT | 1 |
DEF | 1 |
ASD | 2 |
ASD | 2 |
FGH | 1 |
TGY | 1 |
POL | 2 |
POL | 2 |
However i need out put like below
CALL NO. | Sheet1.Count (OUTPUT AFTER GROUOPING AND INDEXING |
ABC | 1 |
PQR | 1 |
ERT | 1 |
DEF | 1 |
ASD | 0 |
ASD | 1 |
FGH | 1 |
TGY | 1 |
POL | 0 |
POL | 1 |
Solved! Go to Solution.
This is a solution in codeform:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMlSK1YlWCggMgrNdg0LgbBdXNzjbMdgFyDbCYLu5e8DVhLhHIsz094GrgbNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"CALL NO." = _t, Count = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"CALL NO.", type text}, {"Count", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"CALL NO."}, {{"Count", each Table.RowCount(_), type number}, {"Partition", each Table.AddIndexColumn(_, "CountMultiple",0,1) , type table}}), #"Expanded Partition" = Table.ExpandTableColumn(#"Grouped Rows", "Partition", {"CountMultiple"}, {"CountMultiple"}), #"Added Custom" = Table.AddColumn(#"Expanded Partition", "Result", each if [Count] > 1 then [CountMultiple] else [Count]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count", "CountMultiple"}) in #"Removed Columns"
But I've also recorded a little screencast where you can see how this is done via the UI (and how to use the code above if that's new to you too 😉 )
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Calling in the calvary...@ImkeF
This is a solution in codeform:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMlSK1YlWCggMgrNdg0LgbBdXNzjbMdgFyDbCYLu5e8DVhLhHIsz094GrgbNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"CALL NO." = _t, Count = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"CALL NO.", type text}, {"Count", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"CALL NO."}, {{"Count", each Table.RowCount(_), type number}, {"Partition", each Table.AddIndexColumn(_, "CountMultiple",0,1) , type table}}), #"Expanded Partition" = Table.ExpandTableColumn(#"Grouped Rows", "Partition", {"CountMultiple"}, {"CountMultiple"}), #"Added Custom" = Table.AddColumn(#"Expanded Partition", "Result", each if [Count] > 1 then [CountMultiple] else [Count]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count", "CountMultiple"}) in #"Removed Columns"
But I've also recorded a little screencast where you can see how this is done via the UI (and how to use the code above if that's new to you too 😉 )
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@ImkeF You're a STAR!!!!
You have no idea how long I've been searching for this!
BUT....
I was following your steps but was stuck as I could not find the "Countmultiple" in the checked boxes when I'm trying to expand it from "Partition"
Note that I could see "Countmultiple" column at the bottom of the screen.
Hello @alexanderc , @jp_powerbiuser , @Dylan73 ,
sorry for the late response.
It just dawned to me that the reason for the behaviour you're seeing lies in a change in the PQ engine that automatically creates a hard-coded table type definition when using the all aggregation. In there, the manually created Index-column ("CountMultiple") is not included and will therefore not be displayed for expansion.
You can solve it by deleting the code that defines the table type (incl. the comma):
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
EXCELLENT! Thank you so much! And it was so simple - but just proves how very little I truly understand about these formulas and how amazing you are for helping. Thank you!
I am also having this issue but can't figure out why, any ideas?
Many thanks 🙂
hi,
after +10 hours of processing this part of the code - = Table.Group(#"Expanded Table Column1", {"Primary Email"}, {{"Count", each Table.RowCount(_), type number}, {"Partition", each _, type table}}) -
the application stopped and displayed this error message: An error occurred in the ‘Transform File from all contacts msd’ query. DataFormat.Error: External table is not in the expected format.
Details: MSD contact Feb 2019.pbix
Any clue?
the dataset is ~3M rows, 40 columns.
thanks a lot
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
95 | |
86 | |
76 | |
64 |
User | Count |
---|---|
138 | |
113 | |
109 | |
98 | |
93 |