Power Query, fxLookup, Wert nachschlagen (ohne Table Join)

 Anbei ein alternativer Ansatz zu einem Table Join (Tabellen über Schlüsselfeld verknüpfen),
um einen Wert in einer anderen Datenquelle / Datei nachzuschlagen

Ausgangssituation 2 Text- Dateien

Ziel:

neue Abfrage erstellen, Quellcode einfügen und Abfrage umbenennen in fxLookup

(Dateipfad anpassen)

--- SCHNIPP ---

let fxLookup = (input) =>

let

    Source = Csv.Document(File.Contents("C:\Users\socia\Desktop\temp_vlookup\StateAbbreviations.txt"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),

    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type"),

    Record = Table.First(Table.SelectRows(#"Promoted Headers", each ([State Name] = input))),

    Result = Record.Field(Record,"State")

in

    Result

in

    fxLookup

--- SCHNAPP ---

neue Abfrage erstellen, Quellcode einfügen

(Dateipfad anpassen)

--- SCHNIPP ---

let

    Source = Csv.Document(File.Contents("C:\Users\socia\Desktop\temp_vlookup\StatePopulations.txt"),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),

    #"Promoted Headers" = Table.PromoteHeaders(Source),

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Rank", Int64.Type}, {"State Name", type text}, {"Population", Int64.Type}},"en_US"),

    #"Uppercased Text" = Table.TransformColumns(#"Changed Type",{{"State Name", Text.Upper}}),

    #"Added Custom4" = Table.AddColumn(#"Uppercased Text", "State5", each fxLookup([State Name]))

in

    #"Added Custom4"

--- SCHNAPP ---

Ergebnis:

Beispieldatei

Excel Arbeitsmappe 

Text-Dateien