Forum Discussion
Create Table in Excel before using in Power BI or Edit Query in Power BI?
Hi Anonymous,
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:
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.
Best Regards,
Qiuyun YuHi Anonymous,
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.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:
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
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! :smileyhappy:
Hi Anonymous,
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 YuThank 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 Anonymous,
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.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:
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