Forum Widgets
Recent Discussions
Appending columns and creating new column of old column headers
I have a table with data imported from multiple weather stations. The imported data has multiple rows of headers - one row giving the location/ID and one row giving the data variable. I have put an example of how the data looks below. I would like to append the columns so there is one column for rainfall etc from all locations. This would require a new column to identify the location for each row. I have tried to see if I can arrange this with Power Query but I can't find a way to get it to recognise two rows of headers. Or is there another way this can be achieved?
Power Query in Excel Fails to Run for Multiple Users
Multiple users are unable to run any Excel Power Query workloads. Error: Could not load file or assembly 'System.Memory, Version=4.0.1.2' Origin: Microsoft.Mashup.Evaluator.Interface Issue affects multiple users and all Power Query refreshes, including simple test queries. Error started on or about August 4th. For reference, current configuration in Excel is: Version 2606 Build 16.0.20131.20150 (64-bit)
Excel (not Power BI) Power Query — 10 min refresh on 49k rows, need faster iteration
I'm hoping someone can point me in a direction I haven't already tried. I've got an Excel workbook (not Power BI) built entirely on Power Query, and refreshes are taking around 10 minutes or more for a dataset that shouldn't warrant that. Some background: the source data is monthly Activity Statement CSVs downloaded from a broker. Each month I drop a new CSV in a folder — sometimes several times a month — and a master query imports and cleans them, currently sitting at 49,000 rows. That master query is the single source everything else builds from: several derivative queries sit on top of it to log trades and calculate tax figures. It needs to refresh reliably every time a new CSV lands, not just monthly. The workbook file itself is small (under 1MB), so it's not a bloated-file problem. The bigger issue right now: at 10 minutes a refresh, I can't actually develop against this workbook. Adding or adjusting a query means refreshing to see if it worked, and that cost makes iterating on new queries impractical. What I've already tried, which helped but hasn't solved it: Found and fixed a few unbuffered Table.NestedJoin merges where the join input was a table derived through several prior steps rather than a raw source — Power Query was silently re-deriving that whole chain once per row being checked. Wrapping the input in Table.Buffer() first fixed three separate cases, confirmed by timing before and after each one. Tried Queries.FastCombine = True via VBA to bypass the Formula Firewall's privacy-level isolation between sources. Result was inconclusive — still slow. Ruled out simple file bloat given the file size. What I haven't been able to pin down, and would really appreciate input on: I've read (and had confirmed via Microsoft Support) that Excel doesn't share a query-evaluation cache across queries in one refresh the way Power BI Desktop does — if two queries reference the same upstream query, that upstream query gets re-executed once per referencing query, every single refresh. Is there genuinely no fix for this in Excel short of materializing results to a static worksheet table? That trade-off is quite costly for me given the numbers feed tax calculations. Has anyone dealt with row-by-row list/filter operations (e.g. List.Contains against another table's values) being a hidden bottleneck at this kind of row count, versus using a proper anti-join? Any experience with OneDrive sync, COM add-ins, or alternate join algorithms (Table.Join with JoinAlgorithm.SortMerge) actually making a measurable difference in Excel specifically? Excel doesn't have Query Diagnostics (that's Power BI Desktop only, confirmed) — how do people actually profile a slow refresh in Excel without it? I've built a rough VBA macro to time steps manually but would love a better approach. More generally: is there a development workflow people use to avoid refreshing the full chain every time while building new queries on top of a large existing one? Any pointers appreciated — happy to share more detail on the M code if useful.
Need help with a merge query/expand table problem
I have a problem a merge query/expand table not displaying the correct data form the merged query after the table has been expanded. The merge is built around 4 columns in each table, and I have tried it both by connecting by the 4 separate columns, then merging this columns into a single column (the state it is currently in) in case there was a problem with the number of columns linked. Here's an example row from the query I am trying to merge into my main query: Here's the merge query set up: Here's an example of what the data looks like after the merge: But here's what it looks like after the table is expanded to add the PERIOD_INDEX field to the table: It seems that something is going wrong with the expand, because the PERIOD_INDEX value of 2 for this example is somehow becoming a 1 when the table is expanded. What am I doing wrong here?
Connection to SAP S4HANA Analytics CDS views
Hi all. I´m trying to connect Power BI desktop with our SAP S4HANA server, using CDS views (Core Data Services). This is the new embedded BW, with realtime views of the database. CDS views are working very well using SAP Analysis for Office, and in Excel or PowerPoint I can connect to the server, open the views and work with them. In Power BI Desktop, I connect to the server (Get Data, SAP BW Application Server connector), and get the list of CDS views. But trying to select any of them, I get always the same message: "The key matched more than one row in the table" (see picture below). Anybody has seen this scenario working? Any idea? Thanks in advance!
How get data directly from SAP ECC
Since the last question someone asked about that was 2 years ago. I wanna know if its possible to get data direclty from the SAP. I am pretty sure that my SAP is ECC, and I wanna know how I can pull direct data from that source. Im looking from ways in the internet and not finding anything guiding me. Thanks in advancePower BI Custom Connector
I'm building a Power BI custom connector for an OData API and would appreciate some guidance from the community. My connector currently allows users to select a table/entity and load data successfully. However, I'd like to enhance the user experience with two additional capabilities: Filter Selection Dropdown Similar to the Advanced Options available in the built-in Web connector, I want to provide users with a dropdown (or similar UI control) to select predefined filter values before the data is loaded. Ideally, the selected value would be passed as an OData filter parameter to the API request. Is there a supported way to create dropdowns or dynamic parameter selection screens in a custom connector? Back Navigation / Re-selection of Table After a user selects a table/entity, I'd like to provide a way for them to go back and choose a different table without having to restart the connection process. Is there any concept of a "Back" button or navigation flow within Power Query custom connectors? If not, what is the recommended approach for handling multi-step selection scenarios? I've reviewed the custom connector documentation but haven't found clear examples covering these UI/navigation requirements. If anyone has implemented something similar or can point me to relevant documentation, sample connectors, or best practices, I'd be very grateful. Thanks in advance!
