“Software is like sex: it’s better when it’s free.”

Linus Torval

“In this world, you get what you pay for.”

Kurt Vonnegut, Cat’s Cradle

Power Pivot je eno izmed treh “power” orodij v Excelu. Uporabno, reklamirano, vendar:

  • ne podpira večih indeksov

  • ne podpira M2M relacij

  • … verjetno še kaj, samo ni tema tega zapisa

Analiza osebnih dohodkov v podjetju Grafen von Cilli

Mesečno računovodstvo pripravi OD v obliki:

| Personal_ID | NETO         | BRUTO        | DODATKI    |
|-------------|--------------|--------------|------------|
| 10000       |  1.316,00 €  |  2.632,00 €  |  87,00 €   |
| ...         |  ....        |  ...         |  ...       |

V skoraj enaki obliki imajo vsak mesec pripravljene osebne podatke zaposlenih.

| Personal_ID | Ime in priimek   | Spol | Posta  | Delovno mesto | .... |
|-------------|------------------|------|--------|---------------|----- |
| 10000       | Frederik Celjski | M    | SI3000 | grof          | ...  |
| 10001       | ...              | ...  | ...    | ...           | ...  |

Ko združiš (pojavi se novo polje Datum) tabele za več mesecev, dobiš obliko :

| Datum  | Personal_ID | NETO         | BRUTO        | DODATKI    |
|--------|-------------|--------------|--------------|------------|
|2019-01 | 10000       |  1.316,00 €  |  2.632,00 €  |  87,00 €   |
|...     | ...         |  ....        |  ...         |  ...       |
|2019-02 | 10000       |  1.349,00 €  |  2.679,00 €  |  12,00 €   |
|...     | ...         |  ....        |  ...         |  ...       |

Enako narediš s tabelo z osebnimi podatki.

V reklami sem prebral, da zaradi Power Pivot več ne bom potreboval VLOOKUP in da se podatke povezuje preko relacij. Bil sem prepričan, da bo delovalo enako kot v SQL in bo dal stavek v nadaljevanju rešitev združevanja tabel:

SELECT *
FROM tblOsebni, tblPlace
WHERE (tblOsebni.DatumF = tblPlace.DatumF ) 
      AND
      (tblOsebni.Personal_ID = tblPlace.Personal_ID)

ali da bom dobil vsote po posameznih kriterij podobno kot z:

SELECT tblOsebni.Spol, SUM(tblPlace.NETO) as PlaceNeto
FROM tblOsebni RIGHT JOIN tblPlace 
               ON  tblOsebni.DatumF = tblPlace.DatumF 
                 AND
                   tblOsebni.Personal_ID = tblPlace.Personal_ID
GROUP BY tblOsebni.Spol;

Kar bi bil hiter ogovor na to, koliko zaslužijo celjski glede na spol.

Pa ni tako enostavno. M2M preprosto ni stvar, ki bi bila v Power Query ali v Power Pivot nekaj naravnega. Različni avtorji ponujajo različne rešitve kot npr. s prehodnimi tabelami ali pa s pomočjo DAX funkcij.

Glede na strukturo podatkov se mi zdi najenostavneje z uvedbo dodatnega indeksa, ki spremeni M2M v O2M ali celo O2O.

Komplicirane besede in kratice za enostaven postopek:
Združi Mesec & Personal_ID v novi indeks, kot:
   "2019-03" in 10001 v "2019-03_10001"
V Power Query dodam nov stolpec po meri in uporabim formulo: 
  ID = [DatumF] &"_" &Text.From([Personal_ID])
  
  Query potem izgleda takole:
  let
    Vir = Excel.CurrentWorkbook(){[Name="tblOsebni"]}[Content],
    #"Spremenjena vrsta" = Table.TransformColumnTypes(Vir,{{"DatumF", type text}, {"Personal_ID", Int64.Type}, {"Ime in priimek", type text}, {"Spol", type text}, {"Posta", type text}, {"Delovno mesto", type text}}),
    #"Dodano po meri" = Table.AddColumn(#"Spremenjena vrsta", "ID", each [DatumF] &"_" &Text.From([Personal_ID])) 
in
    #"Dodano po meri"

Tako dobim nov indeks ID, enaki indeks ustvarim tudi v tabeli za plače in sedaj lahko delam po običajnem postopku v Power Pivot ali pa že v Power Query ustvarim novo poizvedbo s povezavo preko ID. Pa naj bo samo povezava v PP:

Primer rezultatov iz vrtilne tabele

    Vrednosti       
Spol    Vsota BRUTO Procent od skupni BRUTO Štetje BRUTO
F        25.998,00 €     53,99%                  10
M        22.158,00 €     46,01%                   9
vsota    48.156,00 €    100,00%                  19

Delovni zvezek z vzorčnimi podatki in podatkovnim modelom se nahaja TUKAJ.