Lowetsani zambiri kuchokera ku OneDrive ndi SharePoint kupita ku Power Query / BI

Ngati inu kapena kampani yanu mumasunga deta mumtambo wa OneDrive kapena pa portal ya kampani ya SharePoint, kulumikiza mwachindunji pogwiritsa ntchito Power Query mu Excel kapena kuchokera ku Power BI kungakhale kovuta modabwitsa.

Nditakumana ndi vuto ngati lomweli, ndinadabwa kupeza kuti palibe njira “zalamulo” zothetsera vutolo. Pazifukwa zina, mndandanda wazomwe zilipo mu Excel komanso Power BI (pomwe zolumikizira ndizokulirapo) pazifukwa zina sizimaphatikizapo kuthekera kolumikizana ndi mafayilo ndi zikwatu za OneDrive.

Kotero zosankha zonse zomwe zaperekedwa pansipa ndi, ku digiri imodzi kapena imzake, "ndodo" zomwe zimafuna zochepa koma zolemba "kumaliza ndi fayilo". Koma ndodozi zimakhala ndi kuphatikiza kwakukulu - zimagwira ntchito 🙂

Vuto ndi chiyani?

Chiyambi chachifupi kwa iwo omwe anakhala zaka 20 ali chikomokere osati mu phunziro.

OneDrive ndi ntchito yosungira mitambo kuchokera ku Microsoft yomwe imabwera m'njira zingapo:

• OneDrive Personal - kwa ogwiritsa ntchito wamba (osakhala akampani). Amakupatsani 5GB kwaulere + malo owonjezera pamalipiro ochepa pamwezi.
• OneDrive for Business - njira kwa ogwiritsa ntchito makampani ndi olembetsa a Office 365 okhala ndi voliyumu yokulirapo (kuchokera ku 1TB kapena kupitilira apo) ndi zina zowonjezera monga kusungirako mtundu, ndi zina.

Mlandu wapadera wa OneDrive for Business ndikusunga zambiri patsamba lamakampani la SharePoint - muzochitika izi, OneDrive ndi imodzi mwamalaibulale a SharePoint'a.

Mafayilo amatha kupezeka kudzera pa intaneti (https://onedrive.live.com tsamba kapena webusayiti ya SharePoint) kapena kulumikiza zikwatu zosankhidwa ndi PC yanu:

Nthawi zambiri mafodawa amasungidwa mu mbiri ya ogwiritsa pa drive C - njira yopita kwa iwo imawoneka ngati C: OgwiritsaloloweraOneDrive). Pulogalamu yapadera imayang'anira kufunikira kwa mafayilo ndi kulunzanitsa kwa zosintha zonse - АOneDrive gent (mtambo wabuluu kapena imvi kumunsi kumanja kwa chinsalu):

Ndipo tsopano chinthu chachikulu.

Ngati tikufunika kukweza deta kuchokera ku OneDrive kupita ku Excel (kudzera Power Query) kapena Power BI, ndiye kuti tikhoza kufotokozera mafayilo am'deralo ndi mafoda kuti agwirizane ngati gwero mwachizolowezi kudzera. Pezani zambiri - Kuchokera pafayilo - Kuchokera m'buku / Kuchokera mufoda (Pezani Zambiri - Kuchokera pafayilo - Kuchokera m'buku lantchito / Foda)koma sichikhala cholumikizira mwachindunji kumtambo wa OneDrive.

Ndiko kuti, m'tsogolomu, posintha, mwachitsanzo, mafayilo mumtambo ndi ogwiritsa ntchito ena, ife muyenera kulunzanitsa kaye (izi zimachitika kwa nthawi yayitali ndipo sizothandiza nthawi zonse) komanso kokha kenako sinthani funso lathu Kufunsa kwa Mphamvu kapena Model mu Power BI.

Mwachibadwa, funso limabuka: momwe mungatulutsire deta kuchokera ku OneDrive/SharePoint mwachindunji kuti deta ikwezedwe mwachindunji kuchokera pamtambo?

Njira 1: Lumikizani ku bukhu kuchokera ku OneDrive for Business kapena SharePoint

1. Timatsegula bukhulo mu Excel yathu - kopi yapafupi kuchokera ku foda yolumikizidwa ya OneDrive ngati fayilo yokhazikika. Kapena tsegulani tsambalo kaye mu Excel Online, kenako dinani batani Tsegulani mu Excel (Tsegulani mu Excel).
2. Pitani ku Fayilo - Tsatanetsatane (Fayilo - Zambiri)
3. Koperani njira yamtambo yopita kubuku ndi batani kopi njira (Koperani Njira) mutu:

   

4. Mu fayilo ina ya Excel kapena Power BI, komwe mukufuna kudzaza deta, sankhani malamulo Pezani deta - Kuchokera pa intaneti (Pezani Zambiri - Kuchokera pa intaneti) ndi kumata njira kukopera mu adiresi munda.
5. Chotsani kumapeto kwa njira ?webu=1 ndipo dinani OK:

   

6. Pazenera lomwe likuwoneka, sankhani njira yololeza Akaunti ya bungwe (Akaunti ya bungwe) ndipo dinani batani Lowani (Lowani muakaunti):

   

   Lowetsani mawu athu achinsinsi olowera kapena sankhani akaunti yakampani pamndandanda womwe ukuwoneka. Ngati muchita zonse bwino, ndiye kulembedwa Lowani ayenera kusintha kukhala Lowani ngati wogwiritsa ntchito wina (Lowani ndi akaunti ina ya ogwiritsa).
   

7. Dinani batani kugwirizana (Lumikizani).
   

Ndiye chirichonse chiri chofanana ndi kuitanitsa mwachizolowezi kwa bukhu - timasankha mapepala ofunikira, matebulo anzeru kuti alowe kunja, ndi zina zotero.

Njira 2: Lumikizani ku fayilo kuchokera ku OneDrive Personal

Kuti mulumikizane ndi bukhu mumtambo wanu (osakhala wakampani) OneDrive, njirayo idzakhala yosiyana:

1. Timatsegula zomwe zili mufoda yomwe tikufuna patsamba la OneDrive ndikupeza fayilo yomwe yatumizidwa kunja.
2. Dinani kumanja pa izo ndikusankha lamulo Introduction (Ikani) kapena sankhani fayilo ndikusankha lamulo lofananalo pamndandanda wapamwamba:

   

3. Pagawo lomwe likuwoneka kumanja, dinani batani Pangani ndi kukopera code yopangidwa:

   

4.  Matani kachidindo kojambulidwa mu Notepad ndi "malizitsani ndi fayilo":
   
   • Chotsani chilichonse kupatula ulalo wamawu
     
   • Chotsani chipikacho cid=XXXXXXXXXXXX&
   • Mawu osinthika emani on Download
   Chifukwa chake, code source iyenera kuwoneka motere:

   

5. Ndiye zonse ndi zofanana ndi njira yapitayi. Mu fayilo ina ya Excel kapena Power BI, komwe mukufuna kudzaza deta, sankhani malamulo Pezani deta - Kuchokera pa intaneti (Pezani Zambiri - Kuchokera pa intaneti), ikani njira yosinthidwa m'munda wa adilesi ndikudina OK.
6. Pamene chilolezo zenera likuwonekera, kusankha njira Windows ndipo, ngati kuli kofunikira, lowetsani mawu achinsinsi olowera ku OneDrive.
   

Njira 3: Lowetsani zomwe zili mufoda yonse kuchokera ku OneDrive for Business

Ngati mukufuna kudzaza Power Query kapena Power BI zomwe zili mufayilo imodzi, koma chikwatu chonse nthawi imodzi (mwachitsanzo, ndi malipoti), njirayo idzakhala yosavuta:

1. Mu Explorer, dinani kumanja pa chikwatu cholumikizidwa chapafupi ndi ife mu OneDrive ndikusankha Onani patsamba (Onani pa intaneti).
2. Mu adilesi ya msakatuli, lembani gawo loyambirira la adilesi - mpaka mawu / _mapangidwe:

   

3. Mu bukhu la Excel lomwe mukufuna kuyika deta kapena lipoti la Power BI Desktop, sankhani malamulo Pezani Zambiri - Kuchokera Fayilo - Kuchokera ku SharePoint Foda (Pezani Zambiri - Kuchokera pafayilo - Kuchokera ku chikwatu cha SharePoint):

   

   Kenako matani gawo lomwe mwakopera m'gawo la adilesi ndikudina OK:

   

   Ngati zenera lovomerezeka likuwonekera, sankhani mtunduwo Microsoft account (Akaunti ya Microsoft), dinani batani Lowani (Lowani muakaunti), ndiyeno, mutatha kulowa bwino, pa batani kugwirizana (Lumikizani):

   

4. Pambuyo pake, mafayilo onse ochokera ku SharePoint amafunsidwa ndikutsitsidwa ndipo zenera lowonera likuwonekera, pomwe mutha kudina. Sinthani Data (Sinthani Data).
   
5. Kusintha kwina kwa mndandanda wa mafayilo onse ndi kuphatikiza kwawo kumachitika kale mu Power Query kapena Power BI mwanjira yokhazikika. Kuti muchepetse bwalo losakira ku foda yomwe tikufuna, mutha kugwiritsa ntchito fyuluta ndi ndime Njira Yofikira (1) kenako onjezerani zonse zomwe zili m'mafayilo omwe apezeka pogwiritsa ntchito batani lomwe lili pamndandanda Timasangalala (2):

   

• Kusonkhanitsa matebulo kuchokera kumafayilo osiyanasiyana pogwiritsa ntchito Power Query
• Kodi Power Query, Power Pivot, Power BI ndi chiyani komanso momwe angakuthandizireni
• Kusonkhanitsa deta kuchokera pamasamba onse a bukhulo mu tebulo limodzi