Parameteréiere vun Datenweeër an der Power Query

Wann Dir schonn ugefaang hutt d'Tools vum gratis Power Query Add-in am Microsoft Excel ze benotzen, da wäert Dir ganz séier op en héich spezialiséierten, awer ganz heefeg an lästeg Problem begéinen, verbonne mat dauernd Linken op Quelldaten ze briechen. D'Essenz vum Problem ass datt wann Dir an Ärer Ufro op extern Dateien oder Ordner referéiert, da setzt Power Query den absolute Wee fir si am Ufrotext. Alles funktionnéiert gutt op Ärem Computer, awer wann Dir decidéiert e Fichier mat enger Demande un Är Kollegen ze schécken, da wäerte se enttäuscht sinn, well. si hunn en anere Wee fir d'Quelldaten op hirem Computer, an eis Ufro funktionnéiert net.

Wat an esou enger Situatioun ze maachen? Loosst eis dëse Fall méi detailléiert mat dem folgenden Beispill kucken.

Formuléierung vum Problem

Ugeholl mir hunn am Dossier E:Verkafsberichter läit de Fichier Top 100 Produiten.xls, wat en Eroplueden aus eiser Firmendatenbank oder ERP System ass (1C, SAP, etc.) Dëse Fichier enthält Informatiounen iwwer déi populärste Wuerenartikelen a gesäit esou aus:

Et ass wahrscheinlech direkt vun der Fliedermaus kloer datt et bal onméiglech ass mat deem an Excel an dëser Form ze schaffen: eidel Reihen duerch eng mat Daten, fusionéierte Zellen, extra Kolonnen, e Multi-Level Header, etc.

Dofir, nieft dëser Datei am selwechten Dossier erstellen mir eng aner nei Datei Handler.xlsx, an deem mir eng Power Query Ufro erstellen, déi ellen Daten aus der Quell eropluede Datei lueden Top 100 Produiten.xls, a setzt se an Uerdnung:

Eng Ufro un eng extern Datei maachen

De Fichier opmaachen Handler.xlsx, wielt op der Tab Daten Kommando Kritt Daten - Vun Datei - Vun Excel Workbook (Daten - Gitt Daten - Vun Datei - Vun Excel), gitt dann d'Plaz vun der Quelldatei an de Blat deen mir brauchen. Déi gewielte Donnéeë ginn an den Power Query Editor gelueden:

Loosst eis se zréck op normal bréngen:

1. Läschen eidel Linnen mat Home — Zeilen läschen — Eidel Linnen läschen (Home - Ewechzehuelen Reihen - Ewechzehuelen eidel Reihen).
2. Läschen onnéideg erop 4 Linnen duerch Home - Reihen läschen - Top Reihen läschen (Home - Ewechzehuelen Reihen - Ewechzehuelen Top Rows).
3. Huelt déi éischt Zeil op den Dësch Header mam Knäppchen Benotzt déi éischt Zeil als Header Reiter Home (Home - Benotzt déi éischt Zeil als Header).
4. Trennt de fënnefzifferen Artikel vum Produktnumm an der zweeter Kolonn mat dem Kommando opgedeelt Kolonn Reiter Transformatioun (Transforméieren - Split Kolonn).
5. Läschen onnéideg Spalten an ëmbenennen d'Rubriken vun de Rescht fir besser Visibilitéit.

Als Resultat sollte mir déi folgend, vill méi agreabel Bild kréien:

Et bleift fir dësen adel Dësch zréck op d'Blat an eiser Datei eropzelueden Handler.xlsx der Équipe zoumaachen an erofzelueden (Home - Zoumaachen & Lueden) Reiter Home:

Fannt de Wee zu enger Datei an enger Ufro

Loosst eis elo kucken wéi eis Ufro "ënnert der Hood" ausgesäit, an der interner Sprooch agebaut an Power Query mam präzisen Numm "M". Fir dëst ze maachen, gitt zréck op eis Ufro andeems Dir doppelklickt an der rietser Fënster Ufroen a Verbindungen an op der Tab iwwerpréiwen decidéieren Fortgeschratt Redakter (View - Advanced Editor):

An der Fënster déi opmaacht, weist déi zweet Linn direkt en haart kodéierte Wee op eis ursprénglech Eropluedsdatei. Wa mir dës Textstring duerch e Parameter, Variabel oder e Link op eng Excel-Blatzelle ersetzen, wou dëse Wee virgeschriwwe gëtt, da kënne mir et spéider einfach änneren.

Füügt e Smart Dësch mat engem Dateiwee

Loosst eis Power Query fir elo zoumaachen an zréck op eis Datei Handler.xlsx. Loosst eis en neit eidel Blat addéieren an e klengen "Smart" Dësch dran maachen, an der eenzeger Zell vun där de komplette Wee fir eis Quelldatendatei geschriwwe gëtt:

Fir e Smart Dësch aus enger regulärer Gamme ze kreéieren, kënnt Dir d'Tastatur Ofkiirzung benotzen Ctrl+T oder Knäppchen Format als Dësch Reiter Home (Home - Format als Table). D'Kolonntitel (Zell A1) kann absolut alles sinn. Notéiert och datt ech fir Kloerheet den Dësch en Numm ginn hunn Parameteren Reiter Builder (Design).

E Wee aus Explorer kopéieren oder souguer manuell anzeginn ass natierlech net besonnesch schwéier, awer et ass am beschten de mënschleche Faktor ze minimiséieren an de Wee, wa méiglech, automatesch ze bestëmmen. Dëst kann mat der Standard Excel Worksheet Funktioun ëmgesat ginn ZELL (ZELL), déi eng Rëtsch nëtzlech Informatioun iwwer d'Zelle kann ausginn, déi als Argument spezifizéiert ass - och de Wee op déi aktuell Datei:

Wa mir dovun ausgoen datt d'Quelldatendatei ëmmer am selwechten Dossier wéi eise Prozessor läit, da kann de Wee dee mir brauchen duerch déi folgend Formel geformt ginn:

=LEFT(CELL(“Dateiname”); FIND(“[“;ZELL(“Dateiname“))-1)&“Top 100 Produkter.xls”

oder op englesch Versioun:

=LEFT(CELL(«Dateiname»);FIND(«[«;ZELL(«Dateiname»))-1)&»Топ-100 товаров.xls»

... wou ass d'Funktioun LEVSIMV (LÉNKS) hëlt e Stéck Text vum ganze Link bis op d'Ouverture vun der Quadratklammer (dh de Wee an den aktuellen Dossier), an da gëtt den Numm an d'Extensioun vun eiser Quelldatendatei drop gepecht.

Parameteriséiert de Wee an der Ufro

Déi lescht a wichtegst Touch bleift - de Wee op d'Quelldatei an der Ufro ze schreiwen Top 100 Produiten.xls, Referenz op d'Zelle A2 vun eisem erstallten "Smart" Dësch Parameteren.

Fir dëst ze maachen, loosst eis zréck op d'Power Query Ufro an öffnen se erëm Fortgeschratt Redakter Reiter iwwerpréiwen (View - Advanced Editor). Amplaz vun engem Text String-Wee an Zitater "E:VerkafsberichterTop 100 Produkter.xlsx" Loosst eis déi folgend Struktur virstellen:

Excel.CurrentWorkbook(){[Numm = "Astellungen"] [Inhalt]0 {}[Wee zu Quelldaten]

Loosst eis kucken aus wat et besteet:

• Excel.CurrentWorkbook() ass eng Funktioun vun der M Sprooch fir Zougang zum Inhalt vun der aktueller Datei
• {[Numm = "Astellungen"] [Inhalt] - dëst ass e Verfeinerungsparameter zu der viregter Funktioun, wat beweist datt mir den Inhalt vun der "Smart" Tabelle kréien Parameteren
• [Wee zu Quelldaten] ass den Numm vun der Kolonn an der Tabell Parameterenop déi mir bezéien
  
• 0 {} ass d'Zeilnummer an der Tabell Parameterenaus deem mir wëllen Daten huelen. D'Kapsel zielt net an d'Nummeréierung fänkt vun Null un, net vun engem.

Dat ass alles, tatsächlech.

Et bleift ze klicken Freck a kuckt wéi eis Ufro funktionnéiert. Elo, wann Dir de ganzen Dossier mat béide Dateien dobannen op en anere PC schéckt, bleift d'Ufro operationell a bestëmmt de Wee fir d'Donnéeën automatesch.

• Wat ass Power Query a firwat ass et gebraucht wann Dir a Microsoft Excel schafft
• Wéi importéiert e schwiewenden Textschnëtt an Power Query
• Neidesign vun engem XNUMXD Crosstab op eng flaach Table mat Power Query