en en ms fi da da da da ca da en da ms hbs_lat ms ms ms en da da ms ms da es da de ro en da da da da de da da de en it da da da es da da en da da da en da sv de da en da en en en en en da pt fr fr en de en sv da da da da sv da da en en en en en nl en en en en en en en en en en en fr en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en sco en en en en en en en en en en en en en en en en da da da da da en da da da da da en tr da ca en en id en en en en en en en en ms ms ms en en ml da en en nl nl da en da de de en en nl en en nl nl sv en da de de en en nl en en nl nl sv en da de de en en nl en en sv nl da en da de da en en nl en en nl nl sv en da da da en en nl en en nl nl sv en da da da en en nl en en nl nl sv en da de de en en nl en en nl nl da en da de de en en nl en en nl nl da en da de de en en nl en en nl en da ms en en en sv da fr da it de en ms ms en it da da de en en en sv da en da en da da en da da da hbs_lat da de de de el de en en tr en en en en en en en es es fi fr fr fr fr hu it it ja ko da nl nl pl pt pt ru sv th tr zh zh ar he
Getting Started with Power Query – Part II | Microsoft Power BI-blog | Microsoft Power BI
Office 365 + Power BI
Welcome back to our Getting Started with Excel and Power BI series.
Today we will continue with our first Excel feature, Power Query, which allows you to discover, shape and transform your data before importing it into Excel or your Power Pivot data model. In Part I we covered mostly how to connect to different data sources and some quick transformations for your newly found data.
In this post we will cover:
Create and modify a simple formula
Filter, sort and group data
To start this tutorial you’ll need:
Download and Install te Power Query add-in
Power BI Trial tenant
OK, let’s get started. Open te provided Excel file at te end of this post. You can follow te getting started steps with te video or with te text on this post. Once you open te file, select Power Query from te ribbon. If you are on one of te imported tables, you will see te Workbook Queries menu on your right. If you don’t, just click te Workbook Queries button on te Power Query ribbon.
First let’s do an online search to import and edit its query so we can learn how to create and modify formulas:
1. Create and modify a simple formula
1. Select te Power Query tab and perform te following online search: “largest cities south america”
2. Hover over te public data results: ‘Largest Cities…” and select edit, which will take you to te Query Editor
3. Click on te fx icon right next to te formula bar
4. Type te following formula = Table.AddColumn(Source, “Country Code”, each Text.Start([Country], 3)) and click on te check symbol to te left of te formula bar – This formula will extract te first 3 characters of Country and place them in a new column
5. You can modify this formula directly in te formula bar or selecting in Applied Steps te step you want to edit and clicking te gears icon
2. Filter, sort and group data
Filtering on te Query Editor is very similar to Excel. For any given column you can click on te down arrow and start filtering or sorting right away. In our specific case:
1. First, let’s try automatic filtering. Select te Country column, click on te down arrow, deselect (Select All) and choose Brazil, Chile and Ecuador.
2. When you click OK, you’ll have te filtered list.
You can also use te same menu to sort your data (ascending or descending).
Now, let’s try grouping by country:
3. Right click on te country column header and select “Group By”
4. You will see te Group menu. In this case, we’ll just count how many records we have by country. You can also perform other grouping operation such as sum, average, min or max on a field (in this example we could do that with population):
4. Work with columns
You can perform almost any type of operation with columns on te Query Editor to shape and transform your data any way you like it. We did that on step 1 with a simple formula. Let’s try other transformations available within Power Query.
1. First let’s remove columns that we don’t want to use. In this case, holding down te Ctrl key select te Image and Key fields. Then right click on one of them and select Remove Columns on te menu:
2. Now, let’s create a Key with our country code and te city name. In Excel, this would be a text operation with te “&” symbol. In Power Query, this is te same. Click on add custom column on te column menu.
3. On te add custom column menu, write te following expression: [Name]&”-“&[Country Code]. We are joining te city name and te country code with a dash in te middle. Rename te column to “Key”
4. You will see te newly added column after you click OK and te function being showed in te formula bar. Te Power Query Formula Language provides a lot of flexibility to shape and transform your data. You can find more information and references here. In any case, you can use te Query Editor to save these steps through te UI and Power Query will take care of te formulas for you.
5. Finally, you will notice that Power Query is saving all of these steps to be replicated on your data refresh or modified in te Applied Steps panel
There are several ways to combine data in Power Query. We will take a look at 2 of them: Append and Merge
1. First, we will merge this query with another one that has te country population on it. Te file provided already has another query (found with Online Search) with te population of countries called “Population by Country”. Click on te Merge button on te Power Query menu.
2. On te merge menu, select each query and then click on te country field for both. This will be te te field that will define te merge so we can get country population on our original table. Also select “Only include matching rows” so we get population just for te list of countries we have. Before clicking OK Power Query already tells us that we have 40 matching records. Click OK
3. You will get a new Query Editor window with a new column. Click on te corner of te header for this new column and select te fields that you want to merge. In this case, we want population. Then click OK.
4. Now, we have a new table with all te information we had before, plus te population of te country for each record. If we click on close and load, we’ll import this data into Excel.
5. Now, let’s try to append data to our original table. There’s another query in te file called “Largest Cities in Europe”. This query already has te same structure as our original table after adding Country Code and Key, including column names. We achieved this using te Query Editor on a similar online search. You can review te steps editing this provided query. We’ll append this data to our first table. Click on te Append button on te Power Query menu.
6. As before, select te 2 queries that we want to Append (“Largest Cities in South America” and “Largest Cities in Europe”) and then click OK.
7. You will get a new Query Editor window with te appended queries.
Finally, if you signed up for a Power BI trial, you have te option of sharing any of these queries with te rest of your organization. As long as te user has access to te underlying data (in this case public data) they will be able to search for te query with te Online Search feature within Excel and load te data from te query you shared. Let’s share te first table we created: “Largest cities in South America”
1. First of all, you need to be signed in to your Power BI tenant. On te Power Query ribbon, click on te Sign In button
2. Enter your Power BI / Office 365 credentials and sign in
3. Now, let’s make sure te name and description of our query is accurate so other users are able to find it. On te Workbook Queries menu, right click on te “Largest cities in South America” query and select edit
4. Once your are on te Query Editor, click on All Properties on te Query Setting menu on te right. Te you will see te query name and description
5. Everything looks good, so let’s share te query to te Data Catalog. Close te Query Editor and right click again on te query. Then select te Send to Data Catalog option
6. You will get another opportunity to edit te name and description. You can also add a documentation URL in case people want more info or request access to te data. Click on te Sharing menu. Here you can decide who will have access to query you’re sharing. In this case we will go with Everyone in te enterprise. Click Send.
7. Now, your query is live on te Data Catalog and discoverable by anyone based on your description. Let’s do an online search for it and filter by organizational data.
8. Finally, if you want to edit your shared queries you can click on te My Data Catalog Queries button on te Power Query ribbon. From this menu you can delete, load, update and even view statistics for your query.
See Power BI in action
Follow @MicrosoftBI on Twitter
Tilmeld dig Power BI-nyhedsbrevet
Tilmeld dig nedenfor for at modtage de seneste nyheder fra Power BI direkte i din indbakke.
Land Afghanistan Albanien Algeriet Amerikansk Samoa Andorra Angola Anguilla Antarktis Antigua og Barbuda Argentina Armenien Aruba Aserbajdsjan Australien Bahamas Bahrain Bailiwick of Jersey Bangladesh Barbados Belgien Belize Benin Bermuda Bhutan Bolivia Bonaire Bosnien-Hercegovina Botswana Bouvetøen Brasilien Brunei Darussalam Bulgarien Burkina Faso Burundi Cabo Verde Cambodja Cameroun Canada Caymanøerne Chile Cocosøerne (Keelingøerne) Colombia Comorerne Congo Cookøerne Costa Rica Côte d'Ivoire Curaçao Cypern Danmark De Amerikanske Jomfruøer De Amerikanske oversøiske øer De Britiske Jomfruøer De Forenede Arabiske Emirater Den Centralafrikanske Republik Den Demokratiske Republik Congo Den Dominikanske Republik Det Britiske Territorium i Det Indiske Ocean Det palæstinensiske selvstyre Djibouti Dominica Ecuador Egypten El Salvador Eritrea Estland Etiopien Falklandsøerne Fiji Filippinerne Finland Frankrig Fransk Guyana Fransk Polynesien Franske Oversøiske Områder Færøerne Gabon Gambia Georgien Ghana Gibraltar Grenada Grækenland Grønland Guadeloupe Guam Guatemala Guernsey Guinea Guinea-Bissau Guyana Haiti Heard- og McDonaldøerne Honduras Indien Indonesien Iran Irland Island Isle of Man Israel Italien Jamaica Jan Mayen Japan Jordan Juleøen Kasakhstan Kenya Kina Kirgisistan Kiribati Korea Kosovo Kroatien Kuwait Laos Lesotho Letland Libanon Liberia Liechtenstein Litauen Luxembourg Madagaskar Malawi Malaysia Maldiverne Mali Malta Marokko Marshalløerne Martinique Mauretanien Mauritius Mayotte Mexico Mikronesien Moldova Monaco Mongoliet Montenegro Montserrat Mozambique Namibia Nauru Nederlandene Nepal New Zealand Nicaragua Niger Nigeria Niue Nordmakedonien Nordmarianerne Norfolk Island Norge Ny Kaledonien Oman Pakistan Palau Panama Papua Ny Guinea Paraguay Peru Pitcairn Polen Portugal Puerto Rico Qatar Reunion Rumænien Rwanda Saba Saint Barthélemy Saint Kitts og Nevis Saint Lucia Saint Martin Saint Pierre og Miquelon Saint Vincent og Grenadinerne Salomonøerne Samoa San Marino São Tomé og Príncipe SAR Hongkong SAR Macao Saudi-Arabien Schweiz Senegal Serbien Seychellerne Sierra Leone Singapore Sint Eustatius Sint Maarten Slovakiet Slovenien Somalia South Georgia og De Sydlige Sandwichøer Spanien Sri Lanka St. Helena, Ascension, Tristan da Cunha Storbritannien Sudan Surinam Svalbard Sverige Swaziland Sydafrika Sydsudan Syrien Tadsjikistan Taiwan Tanzania Tchad Thailand Timor-Leste Tjekkiet Togo Tokelau Tonga Trinidad og Tobago Tunesien Turkmenistan Turks- og Caicosøerne Tuvalu Tyrkiet Tyskland Uganda Ukraine Ungarn Uruguay USA Usbekistan Vanuatu Vatikanstaten Venezuela Vietnam Wallis og Futunaøerne Yemen Zambia Zimbabwe Ækvatorialguinea Østrig Ålandsøerne
Jeg vil gerne modtage Power BI-nyhedsbrevet. Erklæring om beskyttelse af personlige oplysninger.
Deltagelse kræver, at dine personlige oplysninger overføres til andre lande, som Microsoft driver forretning i, herunder USA. Når du sender denne formular, accepterer du overførslen af dine data uden for Kina. Erklæring om beskyttelse af personlige oplysninger.
Hvad er Power BI?
Power BI er en pakke med virksomhedsanalyseværktøjer til dataanalyse og vidensdeling. Overvåg din virksomhed, og få hurtigt svar med omfattende dashboards, der er tilgængelige på alle enheder.
Få de seneste nyheder fra Power BI-blog
Abonner via mail Du skal angive en gyldig mailadresse Du har angivet en personlig mailadresse. Angiv din arbejds- eller skolemailadresse. Noget gik galt. Prøv igen senere.
Beskyttelse af personlige oplysninger og cookies