Contents
A kusan kowane horo na Power Query, lokacin da muka isa yadda ake sabunta tambayoyin da aka ƙirƙira kuma mutane suka ga yadda sabbin bayanai ke maye gurbin tsofaffin bayanai yayin haɓakawa, ɗaya daga cikin masu sauraren ya tambaye ni: “Shin yana yiwuwa a tabbatar da cewa lokacin sabuntawa, tsoffin bayanan shine Hakanan an ajiye wani wuri kuma duk tarihin sabuntawa ya kasance bayyane?
Tunanin ba sabon abu bane kuma daidaitaccen amsar shi zai zama "a'a" - An saita Query Query ta tsohuwa don maye gurbin tsoffin bayanai tare da sababbi (wanda ake buƙata a mafi yawan lokuta). Koyaya, idan kuna so da gaske, zaku iya kusanci wannan iyakancewa. Kuma hanyar, kamar yadda za ku gani daga baya, abu ne mai sauqi qwarai.
Yi la'akari da misali na gaba.
Bari mu ɗauka cewa muna da fayil daga abokin ciniki azaman bayanan shigarwa (bari mu kira shi, bari mu ce, source) tare da jerin samfuran da yake so ya saya a cikin nau'in tebur mai tsauri "mai wayo" mai suna Aikace-aikace:
A cikin wani fayil (bari mu kira shi ta misali mai karɓar) muna ƙirƙirar tambaya mai sauƙi don shigo da tebur tare da samfurori daga Tushen ta hanyar Bayanai - Samun Bayanai - Daga Fayil - Daga Littafin Ayyukan Excel (Data - Sami bayanai - Daga fayil - Daga littafin aikin Excel) sannan a loda teburin da aka samu zuwa takardar:
Idan a nan gaba abokin ciniki ya yanke shawarar yin canje-canje ga tsari a cikin fayil ɗin sa source, sannan bayan sabunta buƙatarmu (ta danna dama ko ta hanyar Bayanai - Wartsake Duk) za mu ga sabon bayanai a cikin fayil mai karɓar - duk misali.
Yanzu bari mu tabbatar da cewa lokacin da ake sabuntawa, ba a maye gurbin tsofaffin bayanai da sababbi ba, amma ana sanya sabbin a kan tsofaffin - tare da ƙara lokacin kwanan wata, don a iya ganin lokacin da waɗannan takamaiman canje-canjen suka kasance. sanya.
Mataki 1. Ƙara lokacin kwanan wata zuwa ainihin tambayar
Mu buda bukata Aikace-aikaceshigo da bayanan mu daga source, kuma ƙara ginshiƙi tare da kwanan wata na sabuntawa zuwa gare shi. Don yin wannan, za ka iya amfani da button Rukunin al'ada tab Ƙara ginshiƙi (Ƙara shafi - ginshiƙi na al'ada), sannan shigar da aikin Kwanan wata.LocalNow - analogue na aikin Farashin TDATA (YANZU) Microsoft Excel:
Bayan danna kan OK ya kamata ku ƙare da kyakkyawan ginshiƙi kamar wannan (kar ku manta da saita tsarin lokacin kwanan wata tare da gunkin a cikin taken shafi):
Idan kuna so, don farantin da aka ɗora zuwa takardar don wannan ginshiƙi, zaku iya saita tsarin kwanan wata tare da daƙiƙa don daidaito mafi girma (dole ne ku ƙara colon da “ss” zuwa daidaitaccen tsari):
Mataki na 2: Neman tsofaffin bayanai
Yanzu bari mu ƙirƙiri wata tambaya wacce za ta yi aiki azaman ma'auni mai adana tsoffin bayanai kafin sabuntawa. Zaɓi kowane tantanin halitta na tebur da aka samu a cikin fayil ɗin mai karɓar, zaɓi akan shafin data umurnin Daga Tebura/Range (Data - Daga tebur / kewayon) or Tare da ganye (Daga takarda):
Ba mu yin komai tare da tebur da aka ɗora a cikin Query Query, muna kiran tambayar, misali, tsohon data kuma latsa Gida - Rufe kuma ɗauka - Rufe kuma ɗauka zuwa… - Ƙirƙiri haɗi kawai (Gida - Rufe&Load - Rufe & Load zuwa… - Ƙirƙiri haɗi kawai).
Mataki 3. Haɗa tsofaffi da sababbin bayanai
Yanzu koma ga ainihin tambayar mu Aikace-aikace kuma ƙara zuwa gare shi daga ƙasan tsoffin bayanai daga buƙatun buffer na baya tare da umarnin Gida - Ƙara Buƙatun (Gida - Abubuwan Tambayoyi):
Shi ke nan!
Ya rage don komawa zuwa Excel ta hanyar Gida - Rufe kuma zazzagewa (Gida - Rufe&Load) kuma gwada sau biyu don sabunta tsarin mu duka tare da maɓallin Ɗaukaka Duk tab data (Bayanai - Refresh Duk). Tare da kowane sabuntawa, sabon bayanan ba zai maye gurbin tsoffin bayanan ba, amma zai tura shi ƙasa, yana adana tarihin ɗaukakawa gabaɗaya:
Ana iya amfani da irin wannan dabarar lokacin shigo da daga kowane tushe na waje (shafukan Intanet, bayanan bayanai, fayilolin waje, da sauransu) don kiyaye tsoffin dabi'u don tarihi idan kuna buƙata.
- Teburin Pivot a cikin kewayon bayanai da yawa
- Haɗa tebur daga fayiloli daban-daban ta amfani da Query Query
- Ana tattara bayanai daga dukkan zanen gado na littafin cikin tebur guda