Forum Discussion
Excel 2016- Pivot table and Power query
Hi Kaz28EN, thank you for reaching out to the Microsoft Fabric Community Forum.
Please follow below steps:
- Go to Data -> Click Get Data -> From Other Sources -> From Workbook (if pulling from another Excel file). If the data is within the same workbook, select From Table/Range for each sheet.
- In Power Query, go to Home -> Append Queries -> Append as New. Select both tables, click ok.
- Ensure that Date is in Date format, Result Interpretation is numeric, Click ‘Close & Load -> Load’ as a Table in Excel.
- Go to Insert -> Pivot Table -> Select the new consolidated table. Set up the fields.
- Select the Result Interpretation column in the Pivot Table. Go to Home -> Conditional Formatting -> New Rule. Use a formula (According to the need).
- Click Data -> Refresh All whenever new data is added.
Please check the attached document for your reference.
If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.
Hi
Thank you for your response i have tried but the pivot table is bringing up all the sample results as sample results 1, 2, 3 where in the pivot table i would like it under just the one header rather than having to select about 20 sample results, if this makes sense? i Have tried putting all the data into one master sheet but it is still messy on the pivot table.
How do i attach as the system keeps saying XLS not supported?
Hi Kaz28EN , Thank you for reaching out to the Microsoft Community Forum.
Try this:
- Combine your data by, if pulling from another Excel file, Go to Data -> Get Data -> From Other Sources -> From Workbook. if within the same workbook, From Table/Range. In Power Query, use Append Queries -> Append as New to merge tables.
- Unpivot Sample Results in Power Query by opening Power Query: Data -> Queries & Connections -> Right-click Query -> Edit. Select all Sample Result columns, Transform -> Unpivot Columns. Rename the new columns as needed. Close & Load to return the cleaned data to Excel.
- Select the cleaned data in Excel. Insert -> Pivot Table. Set up fields as needed.
- Select the Sample Result column in the Pivot Table. Home -> Conditional Formatting -> New Rule. Use a formula based on your Result Interpretation logic. Data → Refresh All when new data is added to keep everything updated.
If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.