Forum Discussion

SvenJ's avatar
SvenJ
Helper III
4 years ago
Solved

Search for a value in another table

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 t...
Need Help
Tips and Tricks
  • lbendlin's avatar
    lbendlin
    4 years ago

    Hier:

    each Table.SelectRows(BWA

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

     

lbendlin's avatar
lbendlin
Super User
4 years ago

 

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's avatar
SvenJ
Helper III
4 years ago

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's avatar
    lbendlin
    Super User
    4 years ago

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