Forum Discussion

Kaz28EN's avatar
Kaz28EN
Regular Visitor
1 year ago

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...
Need Help
v-hashadapu's avatar
v-hashadapu
Community Support
1 year ago

Hi Kaz28EN, thank you for reaching out to the Microsoft Fabric Community Forum.

 

Please follow below steps:

  1. 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.
  2. In Power Query, go to Home -> Append Queries -> Append as New. Select both tables, click ok.
  3. Ensure that Date is in Date format, Result Interpretation is numeric, Click ‘Close & Load -> Load’ as a Table in Excel.
  4. Go to Insert -> Pivot Table -> Select the new consolidated table. Set up the fields.
  5. Select the Result Interpretation column in the Pivot Table. Go to Home -> Conditional Formatting -> New Rule. Use a formula (According to the need).
  6. 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.

Kaz28EN's avatar
Kaz28EN
Regular Visitor
1 year ago

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?