Thanks, Phil.
Hi Catalin,
I tried to run with full fake data to be able to show you about my queries.
I was able to do add FilterID table and do the list count and distinct list count for with the formula below #"Added Custom4" = Table.AddColumn(#"Added Custom2", "Show 0 if all categries are the same for the same ID", each if List.Count(List.Distinct([Custom][Category])) = 1 then 0 else "more than 1 category for the same id")
When I tried to close and load data to excel, it took me 2 hrs and only 444 rows are uploaded.
During that time of uploading it made me so slow on all other application and I cant use my PC almost at all.
After 2 hrs of trying, I stopped the file and I had nothing there.
I am sure I did something incorrectly. Please check the link and let me know.
Thank you all once again.
Regards,
Aye
Hi Phil and Catalin,
Please let me know whether the link is working or not. I have not shared any from Drop box before.
Thank you.
regards,
Aye
Hi Aye,
You can use this formula to count how many unique categories are in the data table for the same ID:
=SUM(IF(FREQUENCY(IF([ID]=C2, MATCH([Category],[Category],0)),ROW([Category])-ROW(D2)+1),1))
It's an array formula, confirm it with Ctrl+Shift+Enter keys (CSE), not just enter.
It works with your table sent via dropbox, paste it in cell H2 and press CSE keys.
Oh, thank you so much, Catalin.
I think I can see the light at the tunnel 🙂
I will try to work from that in Excel. The formula works perfectly fine.
PS I still need to complete my advance excel course over the Easter Break. 🙂 Hope I will be better after.
Regards,
Aye
Hi Catalin,
Now that you have the full length file and can please check with power query, where did I do incorrectly.
thank you so much.
Regards,
Aye
You never sent your file with queries, you just sent the data.
Hi Catalin,
Once I put the query in my file, I can't save it at all. It took me hours to save the file with query.
Can you please test to put the query on my full length file.
Thank you.
Regards,
Aye
You know that you can cancel a query refresh? Right click the query and choose... Cancel.
