THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Davide Mauri

A place for my thoughts and experiences the Microsoft Data Platform

PowerQuery, ExpandTableColumn and the parent data

I’ve been playing with PowerQuery in the last days and I’ve come across the need of accessing “parent” object data when using the ExpandTableColumn function. The solution is more tricky then one could imagine and I have to say a BIG “THANK YOU” to Miguel Llopis for showing it me.

Now, let’s say you have a bunch of files in a directory. All files has the same structure (csv, json…anything you want) and you need to import all their content in one PowerPivot table.


Of course the PowerQuery “From Folder” option is exactly what you need


And there’s the result


If you then click on the “Content” header  you will have the content of the files merged in just one resulting table.


Cool, isn’t it? Now, let’s say they you also want to have the information about the source of your data. Like, for example, the name of the source file, so that you can know from which file the data comes from. Now things become much more complex since there is no object that allows you to access the “parent object” information. In order to solve this, you have to start to use PowerQuery functions ( page 11).

First of all you need to create a function that will do the same operation you just did for all files in a folder, but limiting it only to a specific file, and turn that file in the parameter of such function. Here’s an example to do that:

(filepath) =>
    Source = Table.FromColumns({Lines.FromBinary(File.Contents(filepath))}),
    SplitColumnDelimiter = Table.SplitColumn(Source,"Column1",Splitter.SplitTextByDelimiter(","),2)

the function will take a file, whose path will be passed in the filepath parameter, turn the file into a table and split the values using comma as a delimiter. Since it is a function, you don’t need to have it visible in an Excel sheet, so you can deselect the “Load To Worksheet” option. Let’s give a name to our function and call it DoStuff.

Now that our function is in place we just have to

  1. Load the folder content again
  2. Create a new column with the file path that will be passed to the freshly created function
  3. Remove all unneeded columns
  4. Create an additional column where we’ll call the created function for each file in the folder
  5. Expand the loaded content into columns

Translated into PowerQuery language it becomes:

    Source = Folder.Files("D:\Temp\PowerQuery"),
    InsertedCustom = Table.AddColumn(Source, "File Path", each [Folder Path] & [Name]),
    RemovedOtherColumns = Table.SelectColumns(InsertedCustom,{"File Path"}),
    InsertedCustom1 = Table.AddColumn(RemovedOtherColumns, "Custom", each DoStuff([File Path])),
    #"Expand Custom" = Table.ExpandTableColumn(InsertedCustom1, "Custom", {"Column1.1", "Column1.2"}, {"Custom.Column1.1", "Custom.Column1.2"})
    #"Expand Custom"

and here’s the result:



Published Wednesday, December 4, 2013 2:56 PM by Davide Mauri
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Keith P said:

I can't believe there are no comments raving about this PowerQuery tip. I've been working on this for a couple of days now, and wasn't able to figure out which "M" command to use to do this. Now it seems like a piece of cake! THANK YOU!

October 30, 2015 5:07 PM

Davide Mauri said:

:) You're welcome!

October 30, 2015 5:17 PM

abx said:


May 31, 2018 11:03 PM

dongdong8 said:



June 29, 2018 3:28 AM

linying123 said:



July 16, 2018 8:55 PM

dongdong8 said:



July 23, 2018 11:35 PM

shenyuhang said:


July 23, 2018 11:43 PM

yaoxuemei said:


August 15, 2018 2:13 AM

chenjinyan said:


August 22, 2018 11:12 PM

shenyuhang said:


August 23, 2018 10:27 PM

xiaozhengzheng said:

November 19, 2018 8:34 PM

Leave a Comment


About Davide Mauri

Director of Software Development & Cloud Infrastructure @ Sensoria, an innovative smart garments and wearable company. After more than 15 year playing with the Microsoft Data Platform, with a specific focus on High Performance databases, Business Intelligence, Data Science and Data Architectures, he's now applying all his skills to IoT, defining architectures to crunch numbers, create nice user experiences and provide meaningful insights, all leveraging Microsoft Azure cloud. MVP on Data Platform since 2006 he has a very strong background development and love both the ER model and OO principles. He is also a fan of Agile Methodology and Automation, which he tries to apply everywhere he can, to make sure that "people think, machines do".

This Blog


Privacy Statement