Shigo da bayanai daga OneDrive da SharePoint zuwa Power Query/BI

Idan ku ko kamfanin ku ke adana bayanai a cikin gajimare na OneDrive ko a cikin tashar tashar kamfanin SharePoint, haɗa kai tsaye zuwa gare ta ta amfani da Query Query a cikin Excel ko daga Power BI na iya zama da ban mamaki.

Sa’ad da na taɓa fuskantar irin wannan batu, na yi mamakin ganin cewa babu hanyoyin “doka” da za a magance ta. Don wasu dalilai, jerin abubuwan da aka samo asali a cikin Excel har ma a cikin Power BI (inda saitin masu haɗawa ya fi girma a al'ada) saboda wasu dalilai ba ya haɗa da ikon haɗi zuwa fayilolin OneDrive da manyan fayiloli.

Don haka duk zaɓuɓɓukan da aka bayar a ƙasa sune, zuwa mataki ɗaya ko wani, "ƙugiya" waɗanda ke buƙatar ƙaramin amma manual "ƙarewa da fayil". Amma waɗannan kullun suna da babban ƙari - suna aiki 🙂

Menene matsalar?

A takaice gabatarwa ga wadanda suka ya shafe shekaru 20 da suka gabata a cikin suma ba a cikin batun ba.

OneDrive sabis ne na ajiyar girgije daga Microsoft wanda ke zuwa cikin dandano da yawa:

• OneDrive Keɓaɓɓen - don masu amfani na yau da kullun (marasa kamfanoni). Suna ba ku 5GB kyauta + ƙarin sarari akan ƙaramin kuɗi kowane wata.
• OneDrive don Kasuwanci - zaɓi don masu amfani da kamfanoni da masu biyan kuɗi na Office 365 tare da girma mafi girma da ake samu (daga 1TB ko ƙari) da ƙarin fasali kamar ajiyar sigar, da sauransu.

Wani lamari na musamman na OneDrive don Kasuwanci yana adana bayanai akan tashar haɗin gwiwar SharePoint - a cikin wannan yanayin, OneDrive shine, a haƙiƙa, ɗayan ɗakunan karatu na SharePoint'a.

Ana iya isa ga fayiloli ko dai ta hanyar haɗin yanar gizo (https://onedrive.live.com site ko rukunin yanar gizon SharePoint) ko ta aiki tare da zaɓaɓɓun manyan fayiloli tare da PC ɗin ku:

Yawancin lokaci waɗannan manyan fayiloli ana adana su a cikin bayanan mai amfani akan drive C - hanyar zuwa gare su tana kama da wani abu C: Masu amfaniSunan mai amfaniOneDrive). Wani shiri na musamman yana lura da dacewa da fayiloli da aiki tare da duk canje-canje - АOneDrive gent (gajimare mai shuɗi ko launin toka a cikin ƙananan kusurwar dama na allo):

Kuma yanzu babban abu.

Idan muna buƙatar loda bayanai daga OneDrive zuwa Excel (ta hanyar Tambayar Wutar Lantarki) ko zuwa Power BI, to ba shakka za mu iya tantance fayilolin gida da manyan fayiloli don daidaitawa azaman tushe ta hanyar da aka saba. Samu bayanai - Daga fayil - Daga littafin / Daga babban fayil (Samu bayanai - Daga fayil - Daga littafin aiki / babban fayil)amma ba zai zama hanyar haɗi kai tsaye zuwa ga girgijen OneDrive ba.

Wato, a nan gaba, lokacin canzawa, misali, fayiloli a cikin gajimare ta wasu masu amfani, mu bukatar fara daidaitawa (wannan yana faruwa na dogon lokaci kuma ba koyaushe dace ba) kuma kawai sai a sabunta tambayar mu Tambayar Wuta ko Model a Power BI.

A zahiri, tambayar ta taso: yadda ake shigo da bayanai daga OneDrive/SharePoint kai tsaye domin a loda bayanan kai tsaye daga gajimare?

Zabin 1: Haɗa zuwa littafi daga OneDrive don Kasuwanci ko SharePoint

1. Muna buɗe littafin a cikin Excel - kwafin gida daga babban fayil ɗin OneDrive mai aiki tare azaman fayil na yau da kullun. Ko kuma bude shafin farko a cikin Excel Online, sannan danna maballin Bude a cikin Excel (Bude a cikin Excel).
2. Ka tafi zuwa ga Fayil - Cikakkun bayanai (Fayil - Bayani)
3. Kwafi hanyar girgije zuwa littafin tare da maɓallin hanyar kwafi (Kwafi Hanyar) a cikin take:

   

4. A cikin wani fayil na Excel ko a cikin Power BI, inda kake son cika bayanan, zaɓi umarni Samu bayanai - Daga Intanet (Sami Bayanai - Daga gidan yanar gizo) kuma liƙa hanyar da aka kwafi a cikin filin adireshin.
5. Share a ƙarshen hanya ?web=1 da kuma danna kan OK:

   

6. A cikin taga da ya bayyana, zaɓi hanyar izini Asusun kungiya (Asusun Kungiyar) kuma danna maɓallin shiga A (Shiga):

   

   Shigar da kalmar wucewa ta aiki ko zaɓi asusun kamfani daga lissafin da ya bayyana. Idan kun yi komai daidai, to rubutun shiga A ya kamata canza zuwa Shiga azaman mai amfani daban (Shiga da sauran asusun mai amfani).
   

7. Latsa maballin connection (Haɗa).
   

Sa'an nan duk abin da yake daidai da shigo da littafi na yau da kullum - muna zaɓar takaddun da ake bukata, tebur mai wayo don shigo da kaya, da dai sauransu.

Zabin 2: Haɗa zuwa fayil daga Keɓaɓɓen OneDrive

Don haɗawa da littafi a cikin girgijen OneDrive na sirri (wanda ba na kamfani ba), hanyar za ta bambanta:

1. Muna buɗe abubuwan da ke cikin babban fayil ɗin da ake so akan gidan yanar gizon OneDrive kuma mu nemo fayil ɗin da aka shigo da shi.
2. Dama danna shi kuma zaɓi umarni Gabatarwa (An saka) ko zaɓi fayil ɗin kuma zaɓi irin wannan umarni a cikin menu na sama:

   

3. A cikin panel da ya bayyana a hannun dama, danna maballin Create kuma kwafi code ɗin da aka samar:

   

4.  Manna lambar da aka kwafi a cikin Notepad kuma "gama da fayil":
   
   • Cire komai sai dai mahaɗin da ke cikin ƙididdiga
     
   • Share toshe cid=XXXXXXXXXXXX&
   • Kalma mai sauyawa embed on download
   A sakamakon haka, lambar tushe yakamata tayi kama da haka:

   

5. Sa'an nan kuma duk abin da yake a cikin hanyar da ta gabata. A cikin wani fayil na Excel ko a cikin Power BI, inda kake son cika bayanan, zaɓi umarni Samu bayanai - Daga Intanet (Sami Bayanai - Daga gidan yanar gizo), manna hanyar da aka gyara a cikin filin adireshi kuma danna Ok.
6. Lokacin da taga izini ya bayyana, zaɓi zaɓi Windows kuma, idan ya cancanta, shigar da kalmar wucewa ta OneDrive.
   

Zabin 3: Shigo da abinda ke cikin babban fayil gabaɗaya daga OneDrive don Kasuwanci

Idan kana buƙatar cika Tambayar Wuta ko Power BI abubuwan da ke cikin ba fayil ɗaya ba, amma babban fayil gabaɗaya a lokaci ɗaya (misali, tare da rahotanni), to tsarin zai zama ɗan sauƙi:

1. A cikin Explorer, danna-dama akan babban fayil ɗin aiki tare na gida mai sha'awarmu a cikin OneDrive kuma zaɓi Duba kan shafin (Duba kan layi).
2. A cikin adireshin adireshin mai binciken, kwafi sashin farko na adireshin - har zuwa kalmar / _tsari:

   

3. A cikin littafin aikin Excel inda kake son loda bayanai ko a cikin rahoton Desktop na Power BI, zaɓi umarni Samu Bayanai - Daga Fayil - Daga Fayil ɗin SharePoint (Samu Bayanai - Daga fayil - Daga babban fayil na SharePoint):

   

   Sannan liƙa guntun hanyar da aka kwafi a cikin filin adireshi kuma danna OK:

   

   Idan taga izini ya bayyana, sannan zaɓi nau'in Microsoft lissafi (Asusun Microsoft), danna maballin shiga A (Shiga), sannan, bayan shiga cikin nasara, akan maɓallin connection (Haɗa):

   

4. Bayan haka, ana buƙatar duk fayiloli daga SharePoint kuma ana zazzage su kuma taga samfoti ya bayyana, inda zaku iya dannawa lafiya. Maida Bayanai (Canja bayanai).
   
5. Ƙarin gyara jerin duk fayiloli da haɗakar su ya rigaya ya faru a cikin Ƙarfin Wuta ko a cikin Power BI a daidaitaccen hanya. Don taƙaita da'irar bincike kawai zuwa babban fayil ɗin da muke buƙata, zaku iya amfani da tacewa ta shafi Hanyar Jaka (1) sannan kuma fadada dukkan abubuwan da ke cikin fayilolin da aka samo ta amfani da maɓallin da ke cikin ginshiƙi Content (2):

   

• Haɗa tebur daga fayiloli daban-daban ta amfani da Query Query
• Menene Tambayar Wuta, Wutar Wuta, Power BI da kuma yadda zasu iya taimaka muku
• Ana tattara bayanai daga dukkan zanen gado na littafin cikin tebur guda