pquery - A collection of functions for use in Excel Power Query

"Do we use Power Pivot or Power Query?" – I get this question all the time when teaching Power BI classes. (Note: This applies in Excel as well as Power BI

The April 2016 Power Query Update was just released for Office 365 subscribers, and I can confirm that it is available to the First Release customers, as I’ve already got it installed.  (If you’re on a later branch it may … Continue reading →

Better way to use parameter tables in Power Query and Power BI: Easier to use and futureproof/robust. Transpose the table and your parameter names equal the

I got a question on the blog recently about creating a banding function in Power Query, or creating buckets for Accounts Receivable transactions.  (30-60 days, 60-90 days, etc..)  As this is something that can be applied to a lot of … Continue reading →

Power BI designer is based upon the Power Query formula language informally known as

A great question come up on the Power Query forum: “I would like to know if there is a way to pass parameters to a Stored Procedure used within a Power Query.” Here are the steps in det…

If there’s one feature of Power Query that’s guaranteed to get Excel users very, very excited indeed it’s the ability to combine data from multiple workbooks into a single table. The bad news is th…

letParamSource = GAIDs[Content],RaramRow = Table.SelectRows(ParamSource,each([Parameter]=ParameterName)),Value=if Table.isEmpty(ParamRow)=truethen nullelse Record.Field(ParamRow{0},"Value"),inValueletSolutionPath = fnGetParameter("File Path"), Source = Folder.Files(SolutionPath & "Data"),#"Combined Binaries" = Binary.Combine(Source[Content]),#"Imported CSV" = Csv.Document(#"Combined Binaries",null,",",null,1252),#"First Row as Header" = Table.PromoteHeaders(#"Imported CSV")in#"First Row as Header"

If you've been using Power BI or you've seen the demos, then like me, you're probably excited about Power Query. As a data nerd, I've been having a lot of

Power Query transformations can be very powerful, but they only work on one data source at a time. Sometimes data providers will only provide their data in discrete chunks, like one category per ta…

Getting Started and Understanding Power Query and its M Language

By Julie Smith I was happy to co-present a session at this week’s Atlanta BI User Group with Rob Volk (@SQL_R) meeting entitled “Harvesting XML Data from the Web with Power Query and Cu…

Editor’s note: The following post was written by SQL Server MVP Rafael Salas Creating Power Query Functions You have seen or started using Power Query for Excel by now. If not you have been missing one of the greatest tools of the new Self-Service Business Intelligence offering Power BI. Power Query has a wide range...

Satalyst BI Specialist: How to use Power Query and M to create a parameterized function and execute it against a list

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server

Every time there

Yesterday I've found a very awesome feature in Power Query (Thanks to Matt Masson). The feature is: you can see list of all functions in the Power Query with enumerations and option types with a

A year ago I wrote a post on loading the M code for a Power Query query from a text file using the Expression.Evaluate() function, but I admit that at the time I didn

by Matt Allington In this post, I am going to show you how simple it is to create a custom calendar using Power Query.  If you follow the steps below, you

UPDATE – there’s now a better way to do this using copy/paste. See Here’s a tip that I found out about on the Power Query Technet forum that I thought was worth repeating. If you ever n…

If you want to collect your regularly loaded data without overwriting it or create a load log that writes the load activites with a

This post explores how we can use my Power Query parameter table technique to create dynamic database queries in Power Query.

I got a great question last month on how to implement a histogram in PowerPivot, similar to this great blog post by Tomislav Piasevoli for Multi Dimensional. I decided to solve the problem in Excel…

Anyone who has tried to do any serious work with Power Pivot and Power Query will know about this problem: you use Power Query to load some tables into the Data Model in Excel 2013; you make some c…

My last 2 blog posts have been about data compression – that theme continues today.  In December I blogged about the basics of compression and impacts that

I don’t normally blog about Excel topics outside of Power Query and Power Pivot, but I think anyone who has ever done any serious work in Excel has found how difficult it is to concatenate more tha…