"Starts with" condition based on a separate table

[BA2022]

Hello,

I'm looking for a formula in PowerBi, using DAX or M code, to solve the following issue.

I have two tables:

1) Reference table which contains a column with numerical values but considered as text (like invoice number e.g. "900010")

2) Fact table with a column which contains the same type of data

Expectation: I want to create one additional column in the fact table that will check all matches based on the reference table, using a "starts with" condition. The outcome should be YES or NO.

Example:

Reference table

Column1

900010

85253645

98663254

Fact table

Column1;Column2(Match)

90001090;YES

55900010;NO

90001080;YES

85253655;NO

98663254;YES

Thanks for the support!

[BA2022]

UP please

[ThxAlot]

let
    Ref = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQwMDA0UIrViVayMDUyNTYzMQVzLC3MzIyNTE2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Fact = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQwMDA0sDRQitWBcSwgHAtTI1NjM1NTiIyFmZmxkamJUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    StartsWith = let ref = Ref[Column1] in Table.AddColumn(Fact, "StartsWith", each List.Contains(ref, [Column1], (x,y) => Text.StartsWith(y, x)))
in
    StartsWith

[BA2022]

Hi @ThxAlot,

I tried to reproduce your formula but I'm facing an error message in the new column.

Could you share the steps you've taken to achieve the above ? (I'm definitely not an expert in PowerQuery)

Thanks for your response and have a nice day !

[Anonymous]

Hi @BA2022 

Please follow these steps:

1.         Query if there is an identical string

Column =
COUNTROWS (
    FILTER (
        'Reference table',
        CONTAINSSTRINGEXACT ( 'Fact table'[Column1], 'Reference table'[Column1] )
    )
)

2.         Returns YES for the same, NO otherwise

Column 2 =
IF ( 'Fact table'[Column] = 1, "YES", "NO" )

3.        Final output

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

^{Best Regards,}

^{Yifan Wang}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[BA2022]

Hi @Anonymous,

Thanks for your quick reply 🙂

I think the CONTAINSSTRINGEXACT function will lead to incorrect data in my file.

I checked this way and actually it really must be a "startswith" condition to ensure accurate data.

I've added one more example below to clarify:

Reference table

Column1

900010

85253645

98663254

Fact table

Column1;Column2(Match)

90001090;YES

90001080;YES

85253655;NO

98663254;YES

55900010;NO