Forum Discussion
Excel 2016- Pivot table and Power query
Dear All I have an excel 2016 spreadsheet where i have information, someone previously set up a power query and pivot table, however i am unable to refresh the data I have changed the file path b...
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.