Show error message when some conditions happen - power query

[Anonymous]

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

[MarcelBeug · Accepted 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.

[Anonymous]

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:

https://social.technet.microsoft.com/Forums/en-US/5ce2da39-d8b5-4ed2-a27f-e237ffa50b80/show-error-wh...

Thanks

[ImkeF]

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. 

[Anonymous]

@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.