none
Show error when some conditions happen RRS feed

  • Question

  • 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


    Michael

    Friday, January 6, 2017 8:08 PM

Answers

  • Not sure how to incorporate in your code, but as an example: the code below generates a table with 10 random numbers with 2 decimals. In case of duplicates, an error is returned, otherwise the table is returned.

    let
        Source = List.Random(10),
        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Rounded Off" = Table.TransformColumns(#"Converted to Table",{{"Column1", each Number.Round(_, 2), type number}}),
        DuplicateCheck = if Table.RowCount(#"Rounded Off") <> Table.RowCount(Table.Distinct(#"Rounded Off")) then error "Duplicates encountered" else #"Rounded Off"
    in
        DuplicateCheck


    Saturday, January 7, 2017 4:44 AM

All replies

  • Not sure how to incorporate in your code, but as an example: the code below generates a table with 10 random numbers with 2 decimals. In case of duplicates, an error is returned, otherwise the table is returned.

    let
        Source = List.Random(10),
        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Rounded Off" = Table.TransformColumns(#"Converted to Table",{{"Column1", each Number.Round(_, 2), type number}}),
        DuplicateCheck = if Table.RowCount(#"Rounded Off") <> Table.RowCount(Table.Distinct(#"Rounded Off")) then error "Duplicates encountered" else #"Rounded Off"
    in
        DuplicateCheck


    Saturday, January 7, 2017 4:44 AM
  • Cross posted: https://community.powerbi.com/t5/Desktop/Show-error-message-when-some-conditions-happen-power-query/m-p/110170#U110170

    Michael: as you posted this question twice: please keep both posts updated with your findings and the eventual solution.

    Saturday, January 7, 2017 9:10 AM
  • Great! Exactly what I needed. Thanks Marcel!

    Michael


    Michael

    Saturday, January 7, 2017 11:22 PM