Dinamikus mapping Power Query-vel

Kővári Attila 2021. május 10.

Adott a következő probléma: A Power BI/Power Pivot táblánk egy konkrét oszlopába egyszer a forrás egyik oszlopát, másszor a másik oszlopát kell beolvasni:

Képzeljen el például egy olyan riportot, amit elő kell állítani angolul és magyarul is, ezért ugyanabba a leírás mezőbe néha angol néha magyar leírásokat kell tölteni függően attól, hogy melyik riportot töltjük. Ráadásul a probléma nem egyedi, nem csak egy tábla töltéséről van szó, tehát nem tudjuk (vagy nem akarjuk) minden egyes táblára külön-külön megírni a Power Query transzformációkat. Egy olyan univerzális megoldást keresünk, amelyik működik minden táblára és attól függően, hogy mit állítunk be a nyelv változóba, más és más oszlopból tölti a táblákat.

Azaz:

  1. lekérdezi a forrástábla oszlopneveit és azokat listába teszi (Table.ColumnNames)
  2. A listában a Nyelv változó értékétől függően eltávolítja az _EN, vagy _HU végződésű oszlopokat (List.RemoveItems)
  3. Beolvassa a forrástábla azon oszlopait, melynek neve megtalálható a listában (Table.SelectColumns)
  4. és a céltábla oszlopneveit átírja nyelvfüggetlenre (lecsapja róluk a _HU vagy _EN végződést) (Table.TransformColumnNames)

Ehhez létre kell hoznunk a következő függvényt (legyen a neve mondjuk Oszlopnevek):

let
// Ez a függvény visszaadja a paraméterként megkapott Excel fájl munkalapján található táblázat oszlopnevei közül azokat, amelyek neve tartalmazza a Nyelv változóban tárolt szöveget. A függvény listával tér vissza
    Source = (Forrasfajl as text, Munkalap as text) => let
       Source = Excel.Workbook(File.Contents(Forrasfajl), null, true), 
       ExcelSheet = Source{[Item=Munkalap,Kind="Sheet"]}[Data],
       #"Promoted Headers" = Table.PromoteHeaders(ExcelSheet, [PromoteAllScalars=true]), 
       // Az oszlopnevekből lista készítése:
       #"Oszlopnevek listába tétele" = Table.ColumnNames(#"Promoted Headers"), 
       // Ha a nyelv = HU, akkor eltávolítjuk azokat az oszlopneveke amelyek tartalmazzák az _EN szöveget és vica versa
       #"Oszlopok eltávolítása" = List.RemoveItems(#"Oszlopnevek listába tétele", List.FindText(#"Oszlopnevek listába tétele", if Nyelv="HU" then "_EN" else "_HU")) //
    in
       #"Oszlopok eltávolítása"
in
    Source

Ezzel kapunk egy listát, amely azon oszlopneveket tartalmazza, amelyeket be kell importálnunk a táblából.

Ezt követően meg kell írnunk azt a Power Query lekérdezést, amely beimportálja a táblát. Ez a tábla tartalmazza mind az _EN, mind a _HU végű oszlopokat, de nekünk csak azokat az oszlopokat kell beimportálnunk, amelyeket a fenti függvény visszaad. Ha a Nyelv változó értéke HU, akkor minden nem _EN-re végződő oszlopot és vica versa. Ezt úgy tudjuk elérni, hogy import után csak azokat az oszlopokat tartjuk meg amelyeket a fenti függvény visszaad:

Table.SelectColumns(#"Promoted Headers",#"Oszlopnevek"(Fajlneve, SheetNeve))

Végül import után minden _HU-ra vagy _EN-re végződő oszlopot átnevezünk (Levágjuk a végükről az _EN, vagy _HU végződést)

Table.TransformColumnNames(#"Oszlopok eltávolítása", each Text.Replace(Text.Replace(_, "_EN", ""), "_HU", ""))

Az egész Power Query transzformáció így néz ki egyben:

let
    Source = Excel.Workbook(File.Contents(FajlNeve), null, true),
    ExcelSheet = Source{[Item=MunkalapNeve,Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Címek_Sheet, [PromoteAllScalars=true]),
    #"Oszlopok eltávolítása" = Table.SelectColumns(#"Promoted Headers",#"Oszlopnevek"(FajlNeve, MunkalapNeve)),
    #"Oszlopok átnevezése" = Table.TransformColumnNames(#"Oszlopok eltávolítása", each Text.Replace(Text.Replace(_, "_EN", ""), "_HU", ""))
in
    #"Oszlopok átnevezése"

 

Kővári Attila - BI projekt

POWER BI WORKSHOP

Tudjon meg többet az itt elhangzottakról! Jöjjön el a 2024. január 24.-i Power BI workshopra vagy rendeljen kihelyezett képzést! Részletek >>

 

Önkiszolgáló BI WORKSHOP

Tudjon meg többet az itt elhangzottakról! Jelentkezzen a 2023. december 4-5.-i Excel Power Pivot videókurzusra!  Részletek >>

 
Már készül a következő cikk. Kérjen értesítést a megjelenéséről itt.

Új hozzászólás