Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
Kaz28EN
Regular Visitor

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 but the data where the the pivot table and power query is from is from is somewhere else and need to change it so i can refresh

 

 

17 REPLIES 17
v-hashadapu
Community Support
Community Support

Hi @Kaz28EN, Hope your issue is solved. If it is, please consider marking the answer 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details.
Thank you.

hI

No this is still not working, when i unpivot and rename it says this name already exists, please advise how i can attach spreadsheet for someone to take a look at this, i would be grateful

Kaz28EN
Regular Visitor

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

 

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.

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:

  1. 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.
  2. 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.
  3. Select the cleaned data in Excel. Insert -> Pivot Table. Set up fields as needed.
  4. 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.

Hi @Kaz28EN, Hope your issue is solved. If it is, please consider marking the answer 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details.
Thank you.

v-hashadapu
Community Support
Community Support

Hi @Kaz28EN, Hope your issue is solved. If it is, please consider marking the answer 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details.
Thank you.

v-hashadapu
Community Support
Community Support

Hi @Kaz28EN, Hope your issue is solved. If it is, please consider marking the answer 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details.
Thank you.

Hi 

No i cant get it work, all i need the spreasheet to supply a pivot table so we can report on different months of what was sampled etc i have tried a new spreadsheet from scratch but i am struggling with it 

v-hashadapu
Community Support
Community Support

Hi @Kaz28EN, Hope your issue is solved. If it is, please consider marking the answer 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details.
Thank you.

v-hashadapu
Community Support
Community Support

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


Thank you @Akash_Varuna for your prompt response.

The error message you're encountering indicates that there is an issue with the connection to the external data source in your Power Query. Specifically, it mentions that the source database or table does not exist, or you do not have access to the data source. Additionally, there is a mention of an argument mismatch in a function.

Please consider following below steps:

  1. Ensure that the file path in the Power Query is correct and points to the right location. If the file has been moved, update the path in the Power Query Editor.
  2. Make sure the file or database you are trying to connect to still exists and has not been deleted or renamed.
  3. Ensure you have the necessary permissions to access the file or database. If it’s on a network drive or SharePoint, verify your access rights.
  4. Open Power Query Editor -> Data -> Queries & Connections -> right click on your query -> Edit
  5. In the Power Query Editor, look for the Source step in the Applied Steps pane on the right. Verify that the source path or connection string is correct.
  6. The error mentions a function expecting between 2 and 4 arguments but receiving 5. Identify the function causing the issue. Review the steps in the query to find where this function is used and correct the number of arguments.
  7. After making the necessary corrections, click Close & Load to save the changes and load the data back into Excel.
  8. Go to the Data tab and click Refresh All to refresh the query and the Pivot Table.
  9. Sometimes, there might be hidden steps in the query that are causing issues. Review all steps carefully.
  10. If the above steps do not resolve the issue, consider recreating the query

 

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.

Thank you for your reply unfortunately i think the data base from the person that set the power query up no longer is valid.

Would anyone be able to assist and set up a new one for me or just even a pivot table.

I need a pivot table to show the months and locations of samples carried out with results so we can evaluate failures etc

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


Please follow below steps to set up Power Query and Pivot Table:

  1. Ensure your data is in a clean and structured format, such as an Excel table or CSV file. The data should include columns like Month, Location, Sample Results and any other relevant columns.
  2. Open Excel and go to the Data tab. Click Get Data and select the appropriate data source. Load the data into Power Query.
  3. Use Power Query to clean and prepare the data. Remove unnecessary columns. Ensure the Month and Location columns are in the correct format. Filter out any irrelevant rows. Click Close & Load to load the cleaned data back into Excel.
  4. Select the cleaned data in Excel. Go to the Insert tab and click Pivot Table. Choose where to place the Pivot Table.
  5. Drag the following fields into the Pivot Table. Rows: Month, Location, Values: Sample Results (set to Count or other aggregation as needed). Use filters or slicers to analyze specific subsets of the data.
  6. If the source data changes, you can refresh the Pivot Table by Going to the Data tab and clicking Refresh All.

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 @Kaz28EN , thank you for reaching out to the Microsoft Fabric Community Forum.


If I am understanding it correctly, you want help setting up pivot table and power query. To do this, please follow above steps (of my previous reply).

For your reference I have attached screenshots of doing this based on a sample data.

vhashadapu_0-1738906213848.png

vhashadapu_3-1738906313651.png

 

vhashadapu_2-1738906264671.png

 

vhashadapu_4-1738906348062.pngvhashadapu_5-1738906372548.pngvhashadapu_6-1738906392688.png

 

If this doesn’t help, please consider sharing your data if possible.

 

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
Regular Visitor

Thanks you for this, however it still keeps bringing up the folllowing. The pivot table and power query was set up by someone else in the microsoft community 

Kaz28EN_0-1737635489217.png

 

Akash_Varuna
Resident Rockstar
Resident Rockstar

Hi , Try these 

Open Queries & Connections from the Data tab, right-click the query, select Edit, and update the file path in the Power Query Editor.

Click Close & Load to save changes after updating the data source.

Ensure the Pivot Table references the updated query by selecting Change Data Source under PivotTable Analyze.

Use Refresh All in the Data tab to refresh both the query and Pivot Table.

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

"); $(".slidesjs-pagination" ).prependTo(".pagination_sec"); $(".slidesjs-pagination" ).append("
"); $(".slidesjs-play.slidesjs-navigation").appendTo(".playpause_sec"); $(".slidesjs-stop.slidesjs-navigation").appendTo(".playpause_sec"); $(".slidesjs-pagination" ).append(""); $(".slidesjs-pagination" ).append(""); } catch(e){ } /* End: This code is added by iTalent as part of iTrack COMPL-455 */ $(".slidesjs-previous.slidesjs-navigation").attr('tabindex', '0'); $(".slidesjs-next.slidesjs-navigation").attr('tabindex', '0'); /* start: This code is added by iTalent as part of iTrack 1859082 */ $('.slidesjs-play.slidesjs-navigation').attr('id','playtitle'); $('.slidesjs-stop.slidesjs-navigation').attr('id','stoptitle'); $('.slidesjs-play.slidesjs-navigation').attr('role','tab'); $('.slidesjs-stop.slidesjs-navigation').attr('role','tab'); $('.slidesjs-play.slidesjs-navigation').attr('aria-describedby','tip1'); $('.slidesjs-stop.slidesjs-navigation').attr('aria-describedby','tip2'); /* End: This code is added by iTalent as part of iTrack 1859082 */ }); $(document).ready(function() { if($("#slides .item").length < 2 ) { /* Fixing Single Slide click issue (commented following code)*/ // $(".item").css("left","0px"); $(".item.slidesjs-slide").attr('style', 'left:0px !important'); $(".slidesjs-stop.slidesjs-navigation").trigger('click'); $(".slidesjs-previous").css("display", "none"); $(".slidesjs-next").css("display", "none"); } var items_length = $(".item.slidesjs-slide").length; $(".slidesjs-pagination-item > button").attr("aria-setsize",items_length); $(".slidesjs-next, .slidesjs-pagination-item button").attr("tabindex","-1"); $(".slidesjs-pagination-item button").attr("role", "tab"); $(".slidesjs-previous").attr("tabindex","-1"); $(".slidesjs-next").attr("aria-hidden","true"); $(".slidesjs-previous").attr("aria-hidden","true"); $(".slidesjs-next").attr("aria-label","Next"); $(".slidesjs-previous").attr("aria-label","Previous"); //$(".slidesjs-stop.slidesjs-navigation").attr("role","button"); //$(".slidesjs-play.slidesjs-navigation").attr("role","button"); $(".slidesjs-pagination").attr("role","tablist").attr("aria-busy","true"); $("li.slidesjs-pagination-item").attr("role","list"); $(".item.slidesjs-slide").attr("tabindex","-1"); $(".item.slidesjs-slide").attr("aria-label","item"); /*$(".slidesjs-stop.slidesjs-navigation").on('click', function() { var itemNumber = parseInt($('.slidesjs-pagination-item > a.active').attr('data-slidesjs-item')); $($('.item.slidesjs-slide')[itemNumber]).find('.c-call-to-action').attr('tabindex', '0'); });*/ $(".slidesjs-stop.slidesjs-navigation, .slidesjs-pagination-item > button").on('click keydown', function() { $.each($('.item.slidesjs-slide'),function(i,el){ $(el).find('.c-call-to-action').attr('tabindex', '-1'); }); var itemNumber = parseInt($('.slidesjs-pagination-item > button.active').attr('data-slidesjs-item')); $($('.item.slidesjs-slide')[itemNumber]).find('.c-call-to-action').attr('tabindex', '0'); }); $(".slidesjs-play.slidesjs-navigation").on('click', function() { $.each($('.item.slidesjs-slide'),function(i,el){ $(el).find('.c-call-to-action').attr('tabindex', '-1'); }); }); $(".slidesjs-pagination-item button").keyup(function(e){ var keyCode = e.keyCode || e.which; if (keyCode == 9) { e.preventDefault(); $(".slidesjs-stop.slidesjs-navigation").trigger('click').blur(); $("button.active").focus(); } }); $(".slidesjs-play").on("click",function (event) { if (event.handleObj.type === "click") { $(".slidesjs-stop").focus(); } else if(event.handleObj.type === "keydown"){ if (event.which === 13 && $(event.target).hasClass("slidesjs-play")) { $(".slidesjs-stop").focus(); } } }); $(".slidesjs-stop").on("click",function (event) { if (event.handleObj.type === "click") { $(".slidesjs-play").focus(); } else if(event.handleObj.type === "keydown"){ if (event.which === 13 && $(event.target).hasClass("slidesjs-stop")) { $(".slidesjs-play").focus(); } } }); $(".slidesjs-pagination-item").keydown(function(e){ switch (e.which){ case 37: //left arrow key $(".slidesjs-previous.slidesjs-navigation").trigger('click'); e.preventDefault(); break; case 39: //right arrow key $(".slidesjs-next.slidesjs-navigation").trigger('click'); e.preventDefault(); break; default: return; } $(".slidesjs-pagination-item button.active").focus(); }); }); // Start This code is added by iTalent as part of iTrack 1859082 $(document).ready(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); $("#tip2").attr("aria-hidden","true").addClass("hidden"); $(".slidesjs-stop.slidesjs-navigation, .slidesjs-play.slidesjs-navigation").attr('title', ''); $("a#playtitle").focus(function(){ $("#tip1").attr("aria-hidden","false").removeClass("hidden"); }); $("a#playtitle").mouseover(function(){ $("#tip1").attr("aria-hidden","false").removeClass("hidden"); }); $("a#playtitle").blur(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); }); $("a#playtitle").mouseleave(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); }); $("a#play").keydown(function(ev){ if (ev.which ==27) { $("#tip1").attr("aria-hidden","true").addClass("hidden"); ev.preventDefault(); return false; } }); $("a#stoptitle").focus(function(){ $("#tip2").attr("aria-hidden","false").removeClass("hidden"); }); $("a#stoptitle").mouseover(function(){ $("#tip2").attr("aria-hidden","false").removeClass("hidden"); }); $("a#stoptitle").blur(function(){ $("#tip2").attr("aria-hidden","true").addClass("hidden"); }); $("a#stoptitle").mouseleave(function(){ $("#tip2").attr("aria-hidden","true").addClass("hidden"); }); $("a#stoptitle").keydown(function(ev){ if (ev.which ==27) { $("#tip2").attr("aria-hidden","true").addClass("hidden"); ev.preventDefault(); return false; } }); }); // End This code is added by iTalent as part of iTrack 1859082