Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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
hi
this is brill, BUT i have some sales orders that are in the table 7 times. See below.
When i do the final step
Result, I do not get the same response as you
= Table.AddColumn(#"Expanded partition", "Result", each if[Count]>1 then [CountMultiple] else [Count])
Result
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 April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
98 | |
61 | |
47 | |
36 | |
34 |