მულტიფორმატიანი ცხრილების აგება ერთი ფურცლიდან Power Query-ში

პრობლემის ფორმულირება

როგორც შეყვანის მონაცემი, გვაქვს Excel ფაილი, სადაც ერთ-ერთი ფურცელი შეიცავს რამდენიმე ცხრილს გაყიდვების მონაცემებით შემდეგი ფორმის:

Გაითვალისწინე:

• სხვადასხვა ზომის ცხრილები და პროდუქტებისა და რეგიონების სხვადასხვა ნაკრები რიგებში და სვეტებში ყოველგვარი დახარისხების გარეშე.
• ცხრილებს შორის შესაძლებელია ცარიელი ხაზების ჩასმა.
• ცხრილების რაოდენობა შეიძლება იყოს ნებისმიერი.
  

ორი მნიშვნელოვანი ვარაუდი. ვარაუდობენ, რომ:

• ყოველი ცხრილის ზემოთ, პირველ სვეტში, არის მენეჯერის სახელი, რომლის გაყიდვებიც ცხრილი ასახავს (ივანოვი, პეტროვი, სიდოროვი და ა.შ.)
  
• ყველა ცხრილში საქონლისა და რეგიონის სახელწოდება იწერება ერთნაირად - ქეისის სიზუსტით.
  

საბოლოო მიზანია ყველა ცხრილიდან მონაცემების შეგროვება ერთ ბრტყელ ნორმალიზებულ ცხრილში, რომელიც მოსახერხებელია შემდგომი ანალიზისთვის და შეჯამების შესაქმნელად, ე.ი.

ნაბიჯი 1. დაუკავშირდით ფაილს

მოდით შევქმნათ ახალი ცარიელი Excel ფაილი და ავირჩიოთ იგი ჩანართზე თარიღი Command მიიღეთ მონაცემები – ფაილიდან – წიგნიდან (მონაცემები - ფაილიდან - სამუშაო წიგნიდან). მიუთითეთ საწყისი ფაილის ადგილმდებარეობა გაყიდვების მონაცემებით და შემდეგ ნავიგატორის ფანჯარაში აირჩიეთ ჩვენთვის საჭირო ფურცელი და დააწკაპუნეთ ღილაკს მონაცემთა კონვერტაცია (მონაცემების ტრანსფორმაცია):

შედეგად, მისგან ყველა მონაცემი უნდა ჩაიტვირთოს Power Query რედაქტორში:

ნაბიჯი 2. გაასუფთავეთ ნაგავი

წაშალეთ ავტომატურად გენერირებული ნაბიჯები შეცვლილი ტიპი (შეცვლილი ტიპი) и ამაღლებული სათაურები (დაწინაურებული ჰედერები) და მოიშორეთ ცარიელი ხაზები და ხაზები ჯამებით ფილტრის გამოყენებით null и სულ პირველი სვეტით. შედეგად, ჩვენ ვიღებთ შემდეგ სურათს:

ნაბიჯი 3. მენეჯერების დამატება

იმისათვის, რომ მოგვიანებით გავიგოთ, სად არის ვისი გაყიდვები, აუცილებელია ჩვენს ცხრილში დავამატო სვეტი, სადაც თითოეულ რიგში იქნება შესაბამისი გვარი. Ამისთვის:

1. მოდით დავამატოთ დამხმარე სვეტი ხაზების ნომრებით ბრძანების გამოყენებით დაამატეთ სვეტი - ინდექსის სვეტი - 0-დან (სვეტის დამატება - ინდექსის სვეტი - 0-დან).

2. დაამატეთ სვეტი ფორმულით ბრძანებით სვეტის დამატება – მორგებული სვეტი (სვეტის დამატება — მორგებული სვეტი) და იქ წარმოადგინეთ შემდეგი კონსტრუქცია:

ამ ფორმულის ლოგიკა მარტივია - თუ პირველი სვეტის შემდეგი უჯრედის მნიშვნელობა არის "პროდუქტი", მაშინ ეს ნიშნავს, რომ ჩვენ წავაწყდით ახალი ცხრილის საწყისს, ამიტომ ჩვენ ვაჩვენებთ წინა უჯრედის მნიშვნელობას მენეჯერის სახელი. წინააღმდეგ შემთხვევაში ჩვენ არ გამოვაჩენთ არაფერს, ანუ ნულს.

იმისთვის, რომ მივიღოთ მშობელი უჯრედი გვარით, პირველ რიგში მივმართავთ წინა საფეხურის ცხრილს #"ინდექსი დამატებულია"და შემდეგ მიუთითეთ ჩვენთვის საჭირო სვეტის სახელი [სვეტი1] კვადრატულ ფრჩხილებში და ამ სვეტის უჯრედის ნომერი ხვეული ფრჩხილებში. უჯრედის ნომერი ერთით ნაკლები იქნება მიმდინარეზე, რომელსაც ვიღებთ სვეტიდან ინდექსი, შესაბამისად.

3. რჩება ცარიელი უჯრედების შევსება null სახელები უმაღლესი უჯრედებიდან ბრძანებით ტრანსფორმირება – შევსება – ქვევით (ტრანსფორმა - შევსება - დაბლა) და წაშალეთ აღარ საჭირო სვეტი ინდექსებით და რიგებით პირველ სვეტში გვარებით. შედეგად, ჩვენ ვიღებთ:

ნაბიჯი 4. ცალკეულ ცხრილებად დაჯგუფება მენეჯერების მიერ

შემდეგი ნაბიჯი არის თითოეული მენეჯერის რიგების დაჯგუფება ცალკე ცხრილებად. ამისათვის, Transformation ჩანართზე გამოიყენეთ ჯგუფი ბრძანებით (Transform – Group By) და გახსნილ ფანჯარაში აირჩიეთ მენეჯერის სვეტი და ოპერაცია ყველა მწკრივი (ყველა მწკრივი), რათა უბრალოდ შეაგროვოთ მონაცემები რაიმე აგრეგაციის ფუნქციის გამოყენების გარეშე. მათ (ჯამ, საშუალო და ა.შ.). პ.):

შედეგად, ჩვენ ვიღებთ ცალკე ცხრილებს თითოეული მენეჯერისთვის:

ნაბიჯი 5: წყობილი ცხრილების ტრანსფორმაცია

ახლა ჩვენ ვაძლევთ ცხრილებს, რომლებიც დევს მიღებული სვეტის თითოეულ უჯრედში ყველა მონაცემი წესიერ ფორმაში.

პირველ რიგში, წაშალეთ სვეტი, რომელიც აღარ არის საჭირო თითოეულ ცხრილში მენეჯერი. ისევ ვიყენებთ მორგებული სვეტი tab ტრანსფორმაციის (ტრანსფორმა — მორგებული სვეტი) და შემდეგი ფორმულა:

შემდეგ, კიდევ ერთი გამოთვლილი სვეტით, ჩვენ ვზრდით პირველ რიგს თითოეულ ცხრილში სათაურებამდე:

და ბოლოს, ჩვენ ვასრულებთ მთავარ ტრანსფორმაციას - ვხსნით თითოეულ ცხრილს M-ფუნქციის გამოყენებით Table.UnpivotOtherColumns:

სათაურიდან რეგიონების სახელები გადავა ახალ სვეტში და მივიღებთ უფრო ვიწრო, მაგრამ ამავდროულად, უფრო მეტ ნორმალიზებულ ცხრილს. ცარიელი უჯრედებით null იგნორირებულია.

არასაჭირო შუალედური სვეტებისგან თავის დაღწევის შემდეგ, ჩვენ გვაქვს:

ნაბიჯი 6 გააფართოვეთ ჩადგმული ცხრილები

რჩება ყველა ნორმალიზებული ჩადგმული ცხრილის გაფართოება ერთ სიაში, სვეტის სათაურში ორმაგი ისრებით ღილაკის გამოყენებით:

და საბოლოოდ მივიღებთ იმას, რაც გვინდოდა:

შეგიძლიათ მიღებული ცხრილის ექსპორტი უკან Excel-ში ბრძანების გამოყენებით მთავარი — დახურვა და ჩატვირთვა — დახურვა და ჩატვირთვა… (მთავარი — დახურვა და ჩატვირთვა — დახურვა და ჩატვირთვა…).

• შექმენით ცხრილები სხვადასხვა სათაურებით მრავალი წიგნიდან
• მონაცემების შეგროვება ყველა ფაილიდან მოცემულ საქაღალდეში
• წიგნის ყველა ფურცლიდან მონაცემების შეგროვება ერთ ცხრილში