power query

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…

This post solves a tricky issue of removing offset duplicates or, in other words, removing items from a list that exist not only in a different column, but also on different rows. Problem History This data format is based on … Continue reading →

Excel, of course, is quite capable of providing simple statistical summaries and analyses. However, there are occasions when bigger guns must be brought to bear on a statistical problem, and Excel

M-Extensions are a very smart way to run you own M (or R)-function libraries in PowerBI: Those functions will appear as if they were native inbuilt-function

If you want to extract the M-code from multiple Power BI pbix-files at once, the function in this blogpost will get you covered: Inclusive query- & stepname

If you’re interested in cascading parameters and Power BI custom connectors is an option for your project, you should give a try to Navigation Tables. Which soon enough could make their way i…

Here is the recording of my webinar at PASS Excel BI VC. If you missed it, you are encouraged to watch and follow the steps, with the materials that are available here to download. https://datachant.com/public/Webinar – Common Patterns.zip Hope you’ll find it useful. (Star Wars image credit here) Related

Power-Query-Cheat-Sheet - Power Query Cheat Sheet

Ages ago I posted an article on unpivoting subcategories using Power Query. The technique is still valid, but what happens when you have a large data set?