Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

Create Table in Excel before using in Power BI or Edit Query in Power BI?

Hello everyone 🙂

 

I'm new to Power BI.

 

The data that I wish to work with and display in Power BI is provided by a government source every 3 months in Excel spreadsheet format. I need to use data from several sheets within the Excel file.

 

The data is organised into aesthetic tables for the viewing public in the Excel file, but it is not the flat-format that Power BI needs (see image below).

 

Screen Shot 2017-10-25 at 12.13.41 PM.png

 

I know that I must rearrange the table so that all columns of similar format are merged into one column (hope that makes sense).

 

Screen Shot 2017-10-25 at 12.15.31 PM.pngImage from here.

 

Should I arrange the data into flat-format using Excel and the 'Create Table' feature? 

 

Or should I use Power BI's 'Get Data' function, and then use 'Edit Query' in Power BI? I'd love to be able to re-run the saved Edit Query steps when I grab the next spreadsheet in a few months.

 

Thanks everyone 🙂 I really appreciate those with incredible skills in Power BI helping new users like me with what I know may be simple questions.

 

2 ACCEPTED SOLUTIONS
v-qiuyu-msft
Community Support
Community Support

Hi @powerbi-learner,

 

Take the Access to Primary Care as at October 2017 (xlsx, 38 KB) Ethnicity sheet as sample, you can create a table within the Excel by select the valid data and format it as a table, then in Power BI desktop, when you use Excel data source to get data, you can choose this table: 

 

a1.PNG

 

Or you can directly get data from orignal Excel file, then choose the Ethnicity sheet, after import data to Power BI then go to Query Editor. Click on the filter for first column to remove invalid data, choose Use first row as headers, then click Apply change to load this table to data model. 

 

a2.PNGa3.PNG

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi @powerbi-learner

 

If you consider the row contains Maori, Pacific, etc, the data displayed in Excel is in matrix structure, the ‘Group’ (eg Maori, Pacific), Group2(Total Enrolled, Total Population, %) are column group, row group is DHB.

 

The easiest way is use Query Editor to optimize data(also take the Ethnicity sheet as example): 

 

1. After you get data from this sheet, open Query Editor, click Remove Blank Rows, Filter rows to remove invalid rows.
2. Then click Transpose, select the column which contains "Total","Maori" etc and click Fill Down.
3. Promote the first row as header.
4. Remove bottom 4 rows.
5. Select all columns which have the same name as [DHB of Domicile], then click Unpivot Columns.
6. Add a conditional column.

e1.PNG

 

For more information, you can download attached pbix file to have a look. When you open it in your environment, please change data source points to the Excel file in your real location:

e2.PNG

 

 Update: Please attention don't post privacy information data in the forum. You can use dummy data to clarify issue instead. 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-qiuyu-msft
Community Support
Community Support

Hi @powerbi-learner,

 

Take the Access to Primary Care as at October 2017 (xlsx, 38 KB) Ethnicity sheet as sample, you can create a table within the Excel by select the valid data and format it as a table, then in Power BI desktop, when you use Excel data source to get data, you can choose this table: 

 

a1.PNG

 

Or you can directly get data from orignal Excel file, then choose the Ethnicity sheet, after import data to Power BI then go to Query Editor. Click on the filter for first column to remove invalid data, choose Use first row as headers, then click Apply change to load this table to data model. 

 

a2.PNGa3.PNG

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
TedemanCPA
Frequent Visitor

New to this board, but I think it's a good idea to use the Power Query/Edit Query feature.

 

If you do it that way, there's a feature that tracks your "data wrangling" process so that you can run it (kind of like a macro), the next time around.

 

Of course, there's probably several other ways, but my guess is that's the best way to start out, and might be the best overall in the end as well.

Thanks so much for your advice mate!!!! This is really helpful! I really appreciate your help! Smiley Happy

Hi @powerbi-learner,

 

From your description, it seems the issue is solved, right? If it is, would you please mark helpful replies as answers so we can close this thread?  

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you. In my original screenshot you’ll see that in row 4 there are merged columns with headings such as Maori, Pacific etc. 

 

In my original post I was seeking to learn the easiest way to creat a flat table with one column for each type of data.

 

So I expected I’d have a column titled DHB, another titled ‘Group’ (eg Maori, Pacific), another titled ‘Enrolled’, another titled ‘Population’, and another titled ‘% Enrolled’.

 

Could you tell me how best to do that? I understand how to move the text from row 5 into headings, but also need to group by the terms in row 4.

 

Thanks again 🙂

Hi @powerbi-learner

 

If you consider the row contains Maori, Pacific, etc, the data displayed in Excel is in matrix structure, the ‘Group’ (eg Maori, Pacific), Group2(Total Enrolled, Total Population, %) are column group, row group is DHB.

 

The easiest way is use Query Editor to optimize data(also take the Ethnicity sheet as example): 

 

1. After you get data from this sheet, open Query Editor, click Remove Blank Rows, Filter rows to remove invalid rows.
2. Then click Transpose, select the column which contains "Total","Maori" etc and click Fill Down.
3. Promote the first row as header.
4. Remove bottom 4 rows.
5. Select all columns which have the same name as [DHB of Domicile], then click Unpivot Columns.
6. Add a conditional column.

e1.PNG

 

For more information, you can download attached pbix file to have a look. When you open it in your environment, please change data source points to the Excel file in your real location:

e2.PNG

 

 Update: Please attention don't post privacy information data in the forum. You can use dummy data to clarify issue instead. 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks a lot! That’s really helpful mate!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors