Inhalter
Formuléierung vum Problem
Als Inputdaten hu mir eng Excel Datei, wou ee vun de Blieder e puer Dëscher enthält mat Verkafsdaten vun der folgender Form:
Bedenkt datt:
- Dëscher vu verschiddene Gréissten a mat verschiddene Sätze vu Produkter a Regiounen a Reihen a Kolonnen ouni Sortéierung.
- Blank Linnen kënnen tëscht Dëscher agefouert ginn.
- D'Zuel vun den Dëscher kann all sinn.
Zwee wichteg Viraussetzungen. Et gëtt ugeholl datt:
- Iwwer all Dësch, an der éischter Kolonn, ass den Numm vum Manager deem säi Verkaf den Dësch illustréiert (Ivanov, Petrov, Sidorov, etc.)
- D'Nimm vu Wueren a Regiounen an all Dëscher sinn an der selwechter Aart a Weis geschriwwe - mat engem Fall Genauegkeet.
D'ultimativ Zil ass d'Donnéeën vun all Dëscher an eng flaach normaliséierter Tabell ze sammelen, praktesch fir eng spéider Analyse an e Resumé ze bauen, dh an dësem:
Schrëtt 1. Connect op de Fichier
Loosst eis eng nei eidel Excel Datei erstellen a wielt se op der Tab Daten Kommando Kritt Daten - Vun Datei - Vum Buch (Daten - Vun Datei - Vun Aarbechtsbuch). Gitt de Standort vun der Quelldatei mat Verkafsdaten un an dann an der Navigatorfenster wielt de Blat dee mir brauchen a klickt op de Knäppchen Konvertéieren Daten (Daten transforméieren):
Als Resultat sollten all Daten dovun an den Power Query Editor gelueden ginn:
Schrëtt 2. Botzen den Dreck
Automatesch generéiert Schrëtt läschen geännert Typ (Ännert Typ) и Erhiewt Header (Promotioun Header) a läscht eidel Linnen a Linnen mat Gesamtzuelen mat engem Filter wäert и TOTAL vun der éischter Kolonn. Als Resultat kréie mir déi folgend Bild:
Schrëtt 3. Dobäi Manager
Fir spéider ze verstoen, wou hir Verkaf ass, ass et néideg eng Kolonn an eiser Tabell ze addéieren, wou an all Zeil e entspriechende Familljennumm gëtt. Dofir:
1. Loosst eis eng Hëllefskolonn mat Zeilnummeren addéieren andeems Dir de Kommando benotzt Füügt Kolonn - Index Kolonn - Vun 0 (Füügt Kolonn - Index Kolonn - Vun 0).
2. Füügt eng Kolonn mat enger Formel mat dem Kommando Eng Kolonn derbäisetzen - Benotzerdefinéiert Kolonn (Füügt Kolonn - Benotzerdefinéiert Kolonn) an déi folgend Konstruktioun do aféieren:
D'Logik vun dëser Formel ass einfach - wann de Wäert vun der nächster Zell an der éischter Kolonn "Produkt" ass, heescht dat, datt mir um Ufank vun enger neier Tabell getrollt hunn, also weisen mir de Wäert vun der viregter Zell mat der Numm vum Manager. Soss weisen mir näischt, also null.
Fir d'Elterenzelle mam Virnumm ze kréien, bezéie mir als éischt op d'Tabell vum virege Schrëtt #"Index dobäigesat", a gitt dann den Numm vun der Kolonn déi mir brauchen [Kolonn 1] a véiereckege Klammeren an d'Zellnummer an där Kolonn a gekrauselt Klammern. D'Zellnummer wäert ee manner sinn wéi déi aktuell, déi mir aus der Kolonn huelen Indexrespektéiert.
3. Et bleift fir déi eidel Zellen auszefëllen mat wäert Nimm aus méi héije Zellen mam Kommando Transforméieren - Fëllen - Down (Transforméieren - Fëllen - Down) a läscht déi net méi gebraucht Kolonn mat Indizes a Reihen mat Familljenumm an der éischter Kolonn. Als Resultat kréien mir:
Schrëtt 4. Gruppéierung an separat Dëscher vun Manager
De nächste Schrëtt ass d'Reihen fir all Manager an getrennten Dëscher ze gruppéieren. Fir dëst ze maachen, op der Transformatioun Tab, benotzt de Kommando Gruppéieren (Transforméieren - Gruppéieren duerch) an an der Fënster déi opmaacht, wielt d'Manager Kolonn an d'Operatioun All Zeilen (All Zeilen) fir einfach Daten ze sammelen ouni eng Aggregatiounsfunktioun ze benotzen hinnen (Zomm, Moyenne, etc.). P.):
Als Resultat kréie mir separat Dëscher fir all Manager:
Schrëtt 5: Transforméiert Nested Dëscher
Elo gi mir d'Tabellen déi an all Zell vun der resultéierender Kolonn leien All Daten an uerdentlech Form.
Als éischt läscht eng Kolonn déi net méi an all Tabell gebraucht gëtt Manager. Mir benotzen erëm Benotzerdefinéiert Kolonn Reiter Transformatioun (Transforméieren - Benotzerdefinéiert Kolonn) an déi folgend Formel:
Dann, mat enger anerer berechent Kolonn, erhiewen déi éischt Zeil an all Tabell op d'Rubriken:
A schliisslech maache mir d'Haapttransformatioun - entfalen all Dësch mat der M-Funktioun Table.UnpivotOtherColumns:
D'Nimm vun de Regiounen aus dem Header ginn an eng nei Kolonn a mir kréien eng méi schmuel, awer gläichzäiteg eng méi laang normaliséiert Tabell. Eidel Zellen mat wäert ignoréiert ginn.
Onnéideg Zwëschenkolonnen ze läschen, hu mir:
Schrëtt 6 Erweidert Nested Dëscher
Et bleift fir all normaliséiert nestéiert Dëscher an eng eenzeg Lëscht auszebauen andeems Dir de Knäppchen mat duebele Pfeile an der Kolonnheader benotzt:
... a mir kréien endlech wat mir wollten:
Dir kënnt déi resultéierend Tabell zréck op Excel exportéieren andeems Dir de Kommando benotzt Home - Zoumaachen a Lueden - Zoumaachen a Lueden an ... (Home — Close&Load — Close&load to...).
- Bauen Dëscher mat verschiddenen Header aus verschidde Bicher
- Sammelt Daten aus all Dateien an engem bestëmmten Dossier
- Sammelen Daten aus all Blieder vum Buch an een Dësch