![excel query table example excel query table example](https://i.stack.imgur.com/bnE0t.png)
- #EXCEL QUERY TABLE EXAMPLE HOW TO#
- #EXCEL QUERY TABLE EXAMPLE MANUAL#
- #EXCEL QUERY TABLE EXAMPLE CODE#
![excel query table example excel query table example](https://i.ytimg.com/vi/VPn3CvQ9xYg/mqdefault.jpg)
Power Query allows a user to import data into Excel through external sources, such as Text files, CSV files, Web, or Excel workbooks, to list a few. What is Power Query?Īs the name suggests, Power Query is the most powerful data automation tool found in Excel 2010 and later.
![excel query table example excel query table example](https://support.content.office.net/en-us/media/f54e4359-3377-478c-868b-920ec826bb93.png)
Since it is very similar to the Excel interface, many users will find it comfortable. The Power Query interface is user-friendly. The Power Query tool makes it a whole lot easier to perform such tasks.Īn added benefit here is that Power Query is easy to use when compared to other BI tools.
#EXCEL QUERY TABLE EXAMPLE MANUAL#
A lot of precious time is usually spent on repetitive manual work such as cut & paste tasks or combining columns and applying filters. We did not have to write M functions that are not available through the Power Query UI (except Table.ColumnNames() ) or use advanced techniques such as nesting functions inside other functions.Power Query makes solving common data tasks easy.
#EXCEL QUERY TABLE EXAMPLE CODE#
There are multiple ways of solving this problem, but our main goal was to show you that, by knowing Power Query objects such as lists or records, you can achieve more flexible solutions than just by relying on M code written automatically by Power Query. The solution works both for the same or different structures of the nested tables (or records). For this purpose, we used a list type of object.
#EXCEL QUERY TABLE EXAMPLE HOW TO#
In the article, we described how to achieve a flexible solution in expanding columns in Power Query. The GetRecordFields variable returns the list of all the record fields available, which can be used to dynamically expand the RecordDetails column.
![excel query table example excel query table example](https://exceleratorbi.com.au/wp-content/uploads/2017/11/image_thumb-1.png)
#"Expanded DetailsRecord" = Table.ExpandRecordColumn(#"Removed Columns", "RecordDetails", GetRecordFields, GetRecordFields)Īs we can see, there are only two differences in a code: the first one is that instead of Table.ColumnNames() we are using Record.FieldNames(), and instead of expanding the table column, we are expanding the record column in the last step. #"Removed Duplicates" = Table.Distinct(#"Expanded ColNames"),ĬolNames = #"Removed Duplicates" #"Expanded ColNames" = Table.ExpandListColumn(#"Select ColNames", "ColNames"), #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",), #"Invoked Custom Function" = Table.AddColumn(#"Removed Other Columns1", "CleanedTables", each FXClean()), When we expand the CleanedTables column, the M code in the formula bar is the following: The script used for this query can be found at the following link where we also explained the process of invoking a custom function in Power Query. Below is an example of the structure of one of the nested tables: This is achieved by clicking on the expand icon next to the column header. Each row now contains a nested table in a structured format, and we want to expand it to access the nested table structures. In the picture above, we can see a scenario that happens when we invoke a custom function. Below is the visual representation of the problem: This is the common scenario in Power Query that can happen when we merge two queries, invoke a custom function, and in many other scenarios. We will demonstrate the problem with an example of the tables that need to be expanded. In the second part of the article, we will show a more robust technique for dealing with changing structures of the nested tables. In this article, we will show how to solve a common problem of expanding nested tables or records in Power Query, when the column names of the nested objects vary in numbers.