The Power Query OData.Feed function has an option called IncludeAnnotations that allows you to return annotation values from an OData data source. It’s not obvious how to use it though – even if you use this option when connecting, you won’t see the annotation values by default because they are returned as metadata.
Tag Archives: Power Query
Adding Tooltips To Column Headers In The Power Query Editor In Power BI And Excel
Here’s something that will excite all you M nerds out there! Did you know you can make tooltips appear when you hover over column headers in a table in the Power Query Editor in either Power BI Desktop or Excel?
Optimising The Performance Of Power Query Merges In Power BI, Part 3: Table.Join And SortMerge
When you merge data from two queries in the Power Query Editor the M code generated uses the Table.NestedJoin function. There is, however, another M function that can be used to merge data: Table.Join. The interesting thing about this function is that has a parameter that Table.NestedJoin doesn’t have: the joinAlgorithm parameter allows you to specify the algorithm used by the Power Query engine for the merge.
Understanding The “The key didn’t match any rows in the table” Error In Power Query In Power BI Or Excel
One of the most common errors you’ll see when working with Power Query in Power BI or Excel is this:
Expression.Error: The key didn’t match any rows in the table
Adding Your Own Messages To Power Query Query Diagnostics
A quick point: while the Power Query Query Diagnostics functionality is relatively new, it’s based on Power BI/Power Query trace logging that has been around for a while. I’ve just realised that this means you can use the Diagnostics.Trace M function that I blogged about back in 2016 to add your own messages to the output of Query Diagnostics.
An M Function To Help You Explore Power Query Diagnostics Data
Last week’s post showed an M function that took Power Query diagnostics data and formatted in a way that made it suitable for visualisation in a Power BI Decomposition Tree visual. This is great for understanding what’s going on at a high level, but by doing this you also lose a lot of detailed information from the diagnostics logs that could be useful for performance tuning.
Remove Duplicates and Keep the Last Record with Power Query
Today I was helping a customer with a problem that seemed quite simple on the surface. She had a data table containing historical customer sales orders (each customer has many orders on different dates). The objective was to filter this table in Power Query and just load one record for each customer – the one that was the last order date. To illustrate the problem more clearly, I have adapted the scenario using the Adventure Works database.