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 Yu 

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