cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
powerbi-learner
Helper I
Helper I

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.

View solution in original post

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.

View solution in original post

Thanks a lot! That’s really helpful mate!

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors