Search for a value in another table

[SvenJ]

I have a table which has a column called "Konto" and a cell with the value of 8421 in this table.

Now i want to search in another table between two columns (Konto von - Konto bis) and give back the value #BWA-Zeile.

How can i do that in Power Query? I know how to do it as a calculated column, but this value is not in Power Query. Can someone help me?

[SvenJ]

Und an welcher Stelle greift er auf welche Tabelle zu?

[lbendlin · Accepted solution]

Hier:

each Table.SelectRows(BWA

greift er auf die existierende BWA Tabelle zu.  Anbei ein sample PBIX.

[SvenJ]

Many thanks for your help! Vielen lieben Dank!

[SvenJ]

@lbendlin This is not a problem - i can fix it to 8599 and start the other line with 8600

[lbendlin]

That would be better. Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. 

[SvenJ]

This is the table, where i have the values, which i need called "BWA" - i need the value BWA_Zeile, if the value is between "Konto von" and "Konto bis" 

And i need the value in this table called "Import"

[lbendlin]

Kannst Du das bitte in benutzbarem (kopierbarem) Format posten?  Bilder sind nicht sehr hilfreich.

[SvenJ]

Eher so?

Konto	Belegdatum	BU-Schlüssel
8735	01.02.2021
8736	12.07.2021	3
8736	12.07.2021	3
8736	12.07.2021	3
8736	21.10.2021	3

Die zu durchsuchende Tabelle sieht wie folgt aus

#BWA-Zeile	Bezeichnung	Konto von	Konto bis	S/H
1010
1020	Umsatzerlöse	8000	8195	H
1020	Umsatzerlöse	8200	8519	H
1020	Umsatzerlöse	8700	8799	H
1020	Umsatzerlöse	8950	8959	H
1040	Best.Verdg. FE/UE	8960	8989	H
1045	Akt.Eigenleistungen	8990	8999	H

[lbendlin]

Use your existing BWA table and then add this one:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dcmxCcAwDATAVYJqI15vFEWzGO+/RkSMIY3L48aQJ7pLE5iCStAKl8z2zV0wKmJPX5OOw3geJjKzQFPDb+YL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Konto = _t, Belegdatum = _t, #"BU-Schlüssel" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Konto", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(BWA,(k)=>k[Konto von]<=[Konto] and [Konto]<=k[Konto bis])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"#BWA-Zeile"}, {"#BWA-Zeile"})
in
    #"Expanded Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[SvenJ]

Sorry, ich kann dir nicht folgen - was hat die Json damit zu tun. Ich habe eine Tabelle, in der die ganzen Daten gespeichert sind und nicht nur die 4 Zeilen - das war nur ein Ausschnitt. Kannst du mir da helfen?

[lbendlin]

Das ist kein JSON, das ist Power Query M code.

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[lbendlin]

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. 

Your "Konto von" and "Konto bis" value ranges seem to be overlapping? What would be the expected outcome for Kontonummer 8600 ?

[SvenJ]

I would fill down the column "'#BWA_Zeile", means the result should always the column "BWA_Zeile" - in this case "1017"

[lbendlin]

My point is that 8600 should not be present in both ranges.