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.
Hello friends,
My queries have some left outer joins where it is critical for me that the referenced tables (imported from csv files) have unique values.
I don't want to do "Remove Duplicates", instead I would like to add a validation that returns some sort of error message when there are multiple values.
How can I achieve it?
Thanks,
Michael
Solved! Go to Solution.
Cross posted: https://social.technet.microsoft.com/Forums/en-US/5ce2da39-d8b5-4ed2-a27f-e237ffa50b80/show-error-wh...
Michael: as you posted this question twice: please keep both posts updated with your findings and the eventual solution.
Cross posted: https://social.technet.microsoft.com/Forums/en-US/5ce2da39-d8b5-4ed2-a27f-e237ffa50b80/show-error-wh...
Michael: as you posted this question twice: please keep both posts updated with your findings and the eventual solution.
What I needed the most - is to fail the query with a meaningful error message.
Marcel's answer in here is exactly what I needed:
Thanks
To my knowledge, PowerBI doesn't have this kind or error-message. Instead you can create an Error-message-query, that would need to be analyzed. This would return all matches, where more than 1 row would be returned:
let Table1 = #table({"Key1"},{{10},{20},{30},{40}}), Table2 = #table({"Key2"},{{10},{10},{29},{39}}), #"Merged Queries" = Table.NestedJoin(Table1,{"Key1"},Table2,{"Key2"},"NewColumn",JoinKind.LeftOuter), #"Aggregated NewColumn" = Table.AggregateTableColumn(#"Merged Queries", "NewColumn", {{"Key2", List.Count, "Count of Key2"}}), #"Filtered Rows" = Table.SelectRows(#"Aggregated NewColumn", each [Count of Key2] > 1) in #"Filtered Rows"
So you do the same JoinKind.LeftOuter, but instead of using the default-expansion ("Expand"), you choose the "Aggregate"-expansion, which creates the "Table.AggregateTableColumn"-step:
This will return the number of returned rows per (match-) row. Filter this >1.
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
@Anonymous,
If this type of data quality check is critical, you could possibly extend the solution from @ImkeF to generate some metadata for each query per Chris Webb's post (see https://blog.crossjoin.co.uk/2014/11/19/returning-error-messages-as-well-as-results-in-power-query/).
It would (currently) be quite manual and I'm not sure about performance or integration into a published PBI Service, but you could then query that metadata to create an Error Log table similar to below, present it as a table, alert on it after refresh, etc.
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 |
---|---|
106 | |
98 | |
80 | |
67 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |