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
Kaz28EN's avatar
Kaz28EN
Regular Visitor
1 year ago

Hi Thank you for the screen shots, however the sheet is not straight foward, i have created a conditional format for the sampling results dependant on value (key within spreadsheet) and have mutliple worksheets, i am trying to show a pivot table that identifes the site, type of sample (quarterly/resample), sample date, quantity of result interretation (and possibly getting the pivot table to match the conditional format in result interpretation.

 

Thank you in advance

 

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.