[TIP]: INDEX-MATCH in Power Query si Configurare Coloane

Doua add-onuri gratuite oferite de catre Microsoft ce completeaza functionalitatea Excel atat de bine incat incepend cu Excel 2013 Power Pivot vine preinstalat (doar trebuie activat) iar in Excel 2016 Power Query este inclus direct in interfata Excel (nu mai este add-on)

[TIP]: INDEX-MATCH in Power Query si Configurare Coloane

Mesajde Catalin B. » Mar Ian 16, 2018 4:54 pm

O solutie foarte des utiliata in Excel o reprezinta calcularea unui comision variabil bazat pe valoarea vanzarilor, sau aflarea categoriei de varsta corespunzatoare.
Formula Excel va fi de forma:
=INDEX({"1-31 days ";"32-61 days ";"62-92 days ";"93-122 days";"123-153 days";"154-183 days ";"184-214 days";"215-244 days ";"245-275 days";"276-305 days ";"> 365 days "},MATCH(A1,{0;32;62;93;123;154;184;215;245;276;306},1))
In Power Query nu este inca o functie nativa care sa faca acest lucru, dar sunt mai multe variante disponibile pentru a obtine acelasi rezultat.
In fisierul anexat, am folosit urmatoarea interogare:
Cod: Selectaţi tot
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Other Columns" = Table.SelectColumns(Source,Columns[Columns Needed]),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Other Columns",{"Foreign Amount Open"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Month End Date", each DateTime.Date(DateTime.LocalNow())),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Month End Date", type date}, {"Invoice Date", type date}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Days Past Due"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns1", "Days Past Due", each [Month End Date]-DateTime.Date([Due Date])),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom2",{{"Days Past Due", Int64.Type}}),

    // functie incorporata in query
    GroupValue = (x) =>
        let
            LookupTable=Table.Sort(GroupsTable,{{"Days", Order.Descending}})
        in
            List.Skip(Table.ToRecords(LookupTable), each [Days] >= x){0}[Group],


    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "Day Range", each GroupValue([Days Past Due]))
in
    #"Added Custom1"

Evident, partea cea mai interesanta este functia GroupValue incorporata in query, care returneaza rezultatul dorit, in functie de numarul de zile de intarziere:
LookupTable este sortat descendent, (LookupTable=Table.Sort(GroupsTable,{{"Days", Order.Descending}})).
Functia List.Skip va elimina din lista inregistrarile care nu corespund criteriului [Days] >= x, dar este posibil sa contina mai multe inregistrari care corespund criteriului de cautare. Deoarece LookupTable este sortat descendent, doar prima inregistrare din lista produsa de List.Skip este valoarea corecta, valoare pe care o returnam cu {0}[Group], unde [Group] este numele coloanei din LookupTable din care dorim sa fie returnat rezultatul.

Pentru cei interesati, in fisier mai exista o a 2-a varianta, cu functie personalizata (independenta de data asta, nu inclusa in query), poate fi testata inlocuind GroupValue([Days Past Due]) din interogarea de mai sus cu Groups([Days Past Due]). Chiar daca returneaza acelasi rezultat, functia bazata pe List.Skip este de 3 ori mai rapida.
In fisierul anexat, mai este o metoda utila: de configurare a listei de coloane ce trebuie pastrate din datele initiale, de cele mai multe ori nu sunt necesare toate coloanele prezente in datele sursa. Lista de coloane necesare se poate configura in Settings sheet. (unele coloane nu se pot sterge, sunt utilizate in interogare!)
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
Probleme să fie, că soluţii se găsesc...
Catalin B.
Moderator
Moderator
 
Mesaje: 748
Membru din: Vin Sep 09, 2011 4:05 pm
Localitate: Iaşi

Înapoi la Power Query & Power Pivot

Cine este conectat

Utilizatorii ce navighează pe acest forum: Niciun utilizator înregistrat şi 1 vizitator