Do 2010. godine postojala je jasna razlika između analize male i velike količine podataka. Ekcel je bio koristan za tabele do 1 milion podataka, a za više od toga morali ste koristiti čuveni Crystal Report ili sličan BI sistem. Brisanjem granica između IT podrške i poslovnih korisnika počelo je PowerPivot-om o kojem možete više pročitati na stranicama ovog sajta.
Podaci su se sve manje unosili u tabele i sve više podataka je već bilo u bazama podataka i raznim drugim izvorima. Poslovni korisnici imaju sve više podataka i sve veću potrebu za analiziranjem tih podataka. IT podrška postaje usko grlo u poslovanju, što je nateralo Microsoft da uz Office 2010 ponudi dodatke u vidu PowerPivot-a i PowerQuery za preuzimanje, transformaciju i analizu više od 1 milion podataka.
Zašto nam je potreban Power Query?
Pošto je radna tabela u Excel-u ograničena na 1milion redova (1,048,576 redova) osmišljena je Velocity tehnologija. Izrade Pivot tabele nad podacima koji se nalaze u SQL serveru ili nekim drugim tabelarnim formatima kao što su XML, JSON, CSV, TXT, MDB, PDF itd.
Power Query ili u prevodu Moćni upiti su tehnologija koja vam omogućava da ne kopirate podatke u excel i opteretite radnu memoriju, nego samo da uspostavite konekciju sa izvorom podataka. Izvršite potrebna prilagođavanja formata, odaberete potrebne kolone iz tabele jednom rečju transformišete formu podataka i tek tada ih upotrebite za izradu pivot (izvedene) tabele kao formu izveštaja.
Ovaj proces obrade podataka se naziva ETL proces (eng. Extract Transform Load – Izdvoji Transformiši Učitaj) i neophodan je u svakom procesu obrade podataka. Prvi oblik podataka koji dobijemo iz nekog izvora nije u takvom stanju da se može odmah analizirati. Microsoft Excel je otišao korak dalje i dogradio proces pripreme podataka sa još jednim setom aktivnosti, a to je kombinovanje podataka, kao što možete videti na sledećoj ilustraciji.
Pored korisnika Ekcel-a, postoji još jedna kategorija ljudi koji svoj profesionalni život posvećuju analizi podataka: profesionalci za poslovnu inteligenciju (BI). BI je nauka o dobijanju uvida iz velikih količina informacija, a poslednjih godina BI profesionalci su naučili i kreirali mnoge nove tehnike i alate za upravljanje sistemima koji mogu da obrađuju opseg od stotina miliona ili čak milijardi redova. BI sistemi zahtevaju trud mnogih profesionalaca i skup hardver za pokretanje. Oni su moćni, ali su skupi i spori za izgradnju, što je ozbiljan nedostatak.
Povezivanje podataka
Da bi povezali podatke moramo izabrati tabulator Data.
Uvoz podataka pomoću Power Query je jednostavan. Excel pruža mnoge uobičajene veze za prenos podataka kojima se može pristupiti sa kartice Data i koje se mogu pronaći iz komande Get Data.
- Preuzmite podatke iz jedne datoteke kao što je Excel radna sveska, tekstualna ili CSV datoteka, XML i JSON datoteke. Takođe možete da uvezete više datoteka iz date fascikle.
- Preuzmite podatke iz različitih baza podataka kao što su SQL Server, Microsoft Access, Analysis Services, SQL Server Analysis Server, Oracle, IBM DB2, MiSQL, PostgreSQL, Sybase, Teradata i SAP HANA baze podataka.
- Preuzmite podatke od Microsoft Azure
- Dobijajte podatke sa usluga na mreži kao što su Sharepoint, Microsoft Exchange, Dinamics 365, Facebook i Salesforce.
- Preuzmite podatke iz drugih izvora kao što su tabela ili opseg unutar trenutne radne sveske, sa veba, Microsoft Kueri-a, Hadoop-a, OData fida, ODBC-a i OLEDB-a.
- Možemo spojiti dva upita slično kao spajanje dva upita u SQL-u.
- Možemo dodati upit drugom upitu sličnom spoju dva upita u SQL-u.
Napomena : Dostupne opcije veze za prenos podataka će zavisiti od vaše verzije Ekcel-a.
Postoji nekoliko uobičajenih tipova upita dostupnih na najvišem nivou komandi trake koje se nalaze u odelјku Get & Transform na kartici Podaci. Odavde možemo lako pristupiti upitima From Text/CSV, From Veb i From Table/Range. Oni su samo duplirani izvan komande Get Data radi lakšeg korišćenja, jer ćete ih verovatno koristiti češće.
U zavisnosti od toga koji tip veze za prenos podataka izaberete, Ekcel će vas voditi kroz podešavanje veze i možda postoji nekoliko opcija koje možete izabrati tokom procesa.[:en]History of Power Query in Excel Versions: 2010, 2013, 2016, 2019, 2021, 365, for Mac and for web
Until 2010, there was a clear distinction between small and large data analysis. Excel was useful for tables of up to 1 million data, and for more than that you had to use the famous Crystal Report or similar BI system. Erasing the boundaries between IT support and business users started with PowerPivot, which you can read more about on the pages of this site.
Data was less and less entered into spreadsheets and more and more data was already in databases and various other sources. Business users have more and more data and a growing need to analyze that data. IT support is becoming a bottleneck in business, which has forced Microsoft to offer additions in the form of PowerPivot and PowerQuery with Office 2010 to download, transform and analyze more than 1 million data.
Why do we need Power Query?
Since the worksheet in Excel is limited to 1 million rows (1,048,576 rows), Velocity technology was designed. Create Pivot Tables on data stored in SQL Server or other tabular formats such as XML, JSON, CSV, TXT, MDB, PDF, etc.
Power Query or in translation Powerful queries is a technology that allows you not to copy data into excel and burden the working memory, but only to establish a connection with the data source. Make the necessary adjustments to the format, select the necessary columns from the table, transform the data form in one word, and only then use them to create a pivot (derived) table as a report form.
This data processing process is called the ETL process (Extract Transform Load) and is necessary in every data processing process. The first form of data we receive from a source is not in such a state that it can be immediately analyzed. Microsoft Excel went a step further and supplemented the data preparation process with another set of activities, which is combining data, as you can see in the following illustration.
History of Power Query
In addition to Excel users, there is another category of people who dedicate their professional lives to data analysis: business intelligence (BI) professionals. BI is the science of gaining insights from large amounts of information, and in recent years, BI professionals have learned and created many new techniques and tools to manage systems that can handle volumes of hundreds of millions or even billions of rows. BI systems require the effort of many professionals and expensive hardware to run. They are powerful, but expensive and slow to build, which is a serious drawback.
Linking data
To connect the data, we must select the Data tab.
Importing data using Power Query is easy. Excel provides many common data transfer links that can be accessed from the Data tab and found from the Get Data command.
- Download data from a single file such as an Excel workbook, text or CSV file, XML and JSON files. You can also import multiple files from a given folder.
- Download data from various databases such as SQL Server, Microsoft Access, Analysis Services, SQL Server Analysis Server, Oracle, IBM DB2, MiSQL, PostgreSQL, Sybase, Teradata and SAP HANA databases.
- Download data from Microsoft Azure
- Get data from online services like Sharepoint, Microsoft Exchange, Dynamics 365, Facebook and Salesforce.
- Retrieve data from other sources such as a table or scope within the current workbook, from the web, Microsoft Query, Hadoop, OData feed, ODBC, and OLEDB.
- We can join two queries similar to joining two queries in SQL.
- We can add a query to another query similar to joining two queries in SQL.
Note : Available data connection options will depend on your version of Excel.
There are several common query types available at the top level of the ribbon commands found in the Get & Transform section of the Data tab . From here we can easily access From Text/CSV , From Web and From Table/Range queries. They are just duplicated outside of the Get Data command for ease of use, as you will probably use them more often.
Depending on which type of data connection you choose, Excel will guide you through setting up the connection, and there may be several options to choose from during the process.[:]