How to expand a column that cannot be expanded in Power BI and Power Query in Excel Especially when working with JSON-data, you might end up with a column that has elements of mixed types in it. I am expanding a column after merging. power query - expanding new columns. If you don't expand it and then load a query to a worksheet, you see a placeholder value of [Record] for each cell in the column.. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel. For more information, see Work with a List, Record, or Table structured column. function ( table as table, column as text, columnNames as list, optional newColumnNames as nullable list) as table Description = Table.ExpandTableColumn (#"Changed Type", "ToRecipients", {"Name . Here is the output for a single row: Using the UI. I am working with json file and each member can have different properties that Power Query parses to columns. #"Removed Other Columns1"{0}[Transform File from input_modules] For more information, see Aggregate data from a column. This line hard codes the currently existing columns, I would like to use a parallel to the UI's " (All") selection option opening up for new columns appearing in future input files. If you're new to Power Query, I have a free webinar running right now that covers an introduction to Power Query and the other Excel tools like Power Pivot, Power BI, Macros & VBA, pivot tables, and more. Return value . This function is used to create separate column for each field in the record. Transform tab > Any Column group > Unpivot Columns drop down > Unpivot Columns command. I have an XML file that I want to use to load the data in Query Editor. However, when I expand the merged queries, PQ seems to randomize my data. So let's say this is my table in Power Query: ID Value 1 10 2 20 3 30 4 40 5 50 6 60 7 70 8 80 then I'd like to add a Custom Column "Range", with the formula= if [Value]<0.33*MAX([Value]) then . But one of the things I've never called out is that - when you are merging tables - you have the opportunity to aggregate data while expanding columns. Table.ExpandTableColumn(tableas table, columnas text, columnNamesas list, optional newColumnNamesas nullable list) as table About Expands tables in table[column] into multiple rows and columns. Table.ExpandListColumn. Tip With a Table, you can also select Aggregate to aggregate data in different ways, such as Sum or Count. In this new query, hit the Group By button and group by "Region" and add a new column with the operation "All . Table.FromColumns(lists as list, optional columns as any) as table About. Rename this new query to be "Output". So, what is Expand Table Column? Re: Power Query finds and displays merge, but returns null when expanding the table. Edit your main table query, and click Merge Queries again. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. Before expanding the grouped table, Add an Index column starting with 1. When using Power Automate to create automated flows one of the most commonly used connector is Microsoft Dataverse (formerly known as Common Data Service (current environment)).It contains a List rows action which has advanced options like Filter rows, Sort By, Expand Query. This function returns a table which expanded . To see the complete Power Query code, scroll down all the way to the bottom of this blog. It populates the most columns (many I don't need or understand) but I was able to expand any column as text The Expand-command operates on the table-level and not on the row-level, so you need to create a list of column names that contains all column names of all rows. Want to read more about Power Query? So I need it first to scan all the possible column names and then to expand. In the resulting Pivot Column dialog, we confirm that it will use the names in the Label column to create the . So let's add a custom column, and point to the column that contains the lists, which is Column2. If I don't want the whole second table to be expanded (just the A column), will Table.Join also be better in this scenario. Here is the resulting M Code:. ! The core ideas of this function is to get the schema, check the type of the columns and if this type is a Table.Type or an Any.Type then expand this column. Expand this custom column. function ( table as table, column as text) as table. It returns a table with a row for each column. So here is my hypothesis: the power query window loads only parts of both tables, around 200 with a max of 1000 rows. In the attempt below, I am extracting the entire "Name" column. You can press the double arrows in the upper right of your image in that column to expand, but I have a question. Result. For this tutorial, we've seen two methods on how you can extract values with power query. This function returns a table with expanded column for each records. Return value . When expanding related rows from one table to another, the default behavior of Power BI is to generate a call to Table.ExpandTableColumn. Right select the column heading and select Drill Down.. 1. Select the AddRows column. One week later, this becomes a problem, as the previous week remains in the selection, and the new week in the 21 week range remains unselected. 3rd query does similar steps to the above. A complete list of all our Power Query blogs can be found here. I need to filter this to just the Max "C4CRptDate". Dynamically Flatten a Parent-Child Hierarchy using Power Query M Introduction. Select all of the columns. When expanding the joined column, selected columns to expand are hardcoded into the M code. Select the Expand icon next to the Country column header. Please help! powerquery-m Table.ExpandRecordColumn ( Table.FromRecords ( { [ a = [aa = 1, bb = 2, cc = 3], b = 2 ] }), "a", {"aa", "bb", "cc"} ) Recommended content Without question, the Expand feature that shows up when you are merging tables using Power Query is very useful. What the heck is Expand? The expand column - arrows will be missing, but some elements still need to be expanded, like here: The expand column - arrows will be missing, but some elements still need to be expanded, like here: The following function has you covered for this task. I am trying to filter a nested table after a join. 1 with my buddy names and the second with a list of tables containing the student names, so I'm on the right track, I just can't now get the names from the table to a comma separated list. It returns a table with all values in column "Value" and additional . In this I have used Expand Query to get interlinked data between tables. When you expand a Record structured column, the values are displayed as new columns in Data Preview. Selecting expand will create a new source record for each related item, and the only columns that will differ will be the items selected from the related table (Name in our . Syntax =Table.ExpandRecordColumn(table,column,fieldNames,newColumnNames) Arguments Example 1 Split the list column [Name] in the table. - - - - My Courses - - - -Mastering DAX - https://goodly.podia.com/power-bi-dax-coursePower Query Course - https://goodly.podia.com/power-query-course- - - -. Concatenating dynamic column name to table - Power Query. 1. I need to expand all the columns not only from {0} record but from all the records. For more information, see Work with a List, Record, or Table structured column.. Parker here! Published on October 19, 2016. in Business Analytics, Business Intelligence, Video Library. only expanding A2 using Table.NestedJoin and only keeping A2 after the Table.Join operation. Instead of manually rearranging data and repeating the process, Power Query lets you build a set of steps that you can apply to your data repeatedly. So I need it first to scan all the possible column names and then to expand. Click the double-arrow on the table column to expand the rows 2. To get the schema of a M table, I can rely on the function Table.Schema (.). Return a . The table that we're looking for is named "Example_4" and once you get that data inside Power Query / Power BI, create a reference of that query. I am converting that record to a table; in that step a table is created with two columns. For those of you familiar with recursive common table expressions in SQL, iterating through a parent-child hierarchy of data is a fairly straight forward process. It's a way of reaching into your lookup fields and instead of the GUID you can grab out of there any value you want. The new column names cannot duplicate any column in the new table. The second column is called [value] and it contains one record that is a LIST. Here is a gif with only 2 filtered rows, but it shows the behavior: the table contains personal details so I cannot share it or show more columns but it is always the SAME PERSON that disappears after expanding. The highlighted button appears in a variety of scenarios in Power BI and Power Query in Excel. Most methods require . Source = Excel.CurrentWorkbook(){[Name="Genres"]}[Content], To make this happen, we jump a few steps back in our applied steps to the Removed Other Columns1 step. If you click on Record, you see just a list of the fields for that one record.However, we want the values for every record as a table. Check out this quick Power BI/Power Query tutorial on how to expand your tables, lists, and records dynamically! You can also double-click the column header. Ask Question Asked 4 years, 10 months ago. Expand Table Column is a transformation step which happens when you click on the highlighted button. About; . Show activity on this post. But now to my question. The core ideas of this function is to get the schema, check the type of the columns and if this type is a Table.Type or an Any.Type then expand this column. When working with Power Query, you might sometimes need to expand Table or Record columns in order to get to the data you want. Do the same thing, except map the integer column for Class. From the Expand columns dialog box, select only the Country and Division fields. The function does not iterate over the list, and does not expand all of the columns. In the Query, I need to expand only the table columns with a specific name below: view; viewfolder; . Instead of expanding the entire related table I would like to expand only the first row. How to expand a column that cannot be expanded in Power BI and Power Query in Excel Especially when working with JSON-data, you might end up with a column that has elements of mixed types in it. In this video, we are showing a more advanced technique on dynamic table column expansion explained in our previous video - https://www.youtube.com/watch?v=i. I need to expand all the columns not only from {0} record but from all the records. 1st Power query loads data from Table1, Removes certain columns, and Loads the remaining data into Table2. Example. In the first part of this series, you learned how to create an exception reporting for a column in a table using Power Query in Power BI.In the second part, you learned how to initiate that process for all columns in a table.This article continues the section one of creating exception reporting for all columns in a table using Power Query and Power BI. Right select the column heading and select Drill Down.. Power Query expands the table by inserting the specified number of rows In the To Table prompt, for this example, we . Custom.ExpandTable = (table as table, columnNames as list) =>. Automatically expand all fields from a JSON document in Power BI and Power Query. I want to use Power Query to extract a related table using Table.ExpandTableColumn. Purpose of Power Query M Table.ExpandTableColumn Function. Transforming Nested Tables in Power Query. This function is used to expand the table column into multiple rows and columns. In the To Table prompt, for this example, we . There is an Employee table with 1 column: [Employee Names] There is a Project table with 1 column: [Project Names] The goal: make the Project table horizontally expand/contract with [Employee Names] so the user can enter in staff hours, e.g. Here's the code: #"Expanded Shifts" = Table.ExpandTableColumn(#"Merged Queries", "Shifts", {"Personnel Number"}, {"Personnel number"}) Rename a column. Save and close this query, and give it a relevant name to distinguish from your other query. . If you click on Record, you see just a list of the fields for that one record.However, we want the values for every record as a table. Purpose of Power Query M Table.ExpandRecordColumn Function. When I ran this query, though, I caught sight of something that is every Power Query developer's worst nightmare: Given a column of lists in a table, create a copy of a row for each value in its list. You can see this in the generated formula field. Select all of the columns. Load the source table into PowerQuery. It returns a table with a row for each column. Select a column, and then select Transform > Rename. So, we want to dynamically expand our table based on all found columns in all tables. This can be done with List.Union: Table.ExpandTableColumn (myTable, "Get Excel Data", List.Union (myTable [Excel Table Columns])) M. You've expanded SLACategory and Order ID (in the top half of your screenshot), but these should probably be SLA Category and OrderId respectively (in the bottom half of your screenshot). Expand it, and choose the Display field only. From the menu select Transform and from the Structure Column section select Expand. Custom.ExpandTable = (table as table, columnNames as list) =>. Hello, I have a table in a Power Query, and would like to reference the maximum value of another column. Example 1. = Table.ExpandTableColumn ( #"Renamed Columns", "Data", ColNames ) Hey guys! Finally, all you need to do now is expand the AllRowsGrouped column and select all the columns except the one you grouped on, which in this case was Person. If appears to return a table of tables using the "Transform File from input_modules" function which is the 5th proper line down from the top. Select Table to see the values. When you click on the expend button, the tables in the relevant column will get expended. This works about as well as recent sources. Installing Power Query. When using Power Query (Get & Transform) in Power BI and Excel, you have the ability to merge queries together and expand the dataset to view all the columns in your merged dataset. Table.ExpandListColumn ( table as table, column as text) as table About Given a table, where a column is a list of values, splits the list into a row for each value. In the Table column drop-down list: Conclusion. This means that the fourth column is made up of MULTIPLE columns that you can drill into. 2nd Power Query loads data from Table2, Removes certain columns, and Loads remaining data into Table3. This function is used to expand columns from the given table. I have mismatched data lines in a power query so I am attempting to renumber/reorganize the data then merge the information to realign. Options are available to either expand or aggregate the related items. I think there are some differences in the headers present in your tables (that you want to expand) and the headers you're actually expanding. The critical part is the "Table.ExpandTableColumn" that is taken from the UI. (Power Query) Is there a way to filter a merged table column without expanding it? Conclusion. Table is an example of a structured column that you can expand to see more columns. With the knowledge of Power Query objects such . Note the differences in spacing and capitalisation. The Table.Join will expand the whole second table. Specify newColumnNamesto avoid conflicts between existing columns and new columns. In the Data Preview, select the Expand icon next to the NewColumn column header. Remove the "Attribute" column.. I created a list with a Choice column that allowed multiple items and it comes in Power Query as a List, not a Table. Most people are aware that Power Query lets you work with tables that have columns containing other "inner" tables. The joined table brings in transnational data and I only need the information from the most resent report. Example 1 Expand column [a] in the table ( { [a = [aa = 1, bb = 2, cc = 3], b = 2]}) into 3 columns "aa", "bb" and "cc". While expanding it expands like a whole number and the relationships don't work anymore. In Data Preview, scroll to the right and click the expand icon next to the Order_Details column. Right select List in the new table and select To Table.. Call the function once per row on the table returned by step (1) Use the Expand/Aggregate button to expand the new column created by step (3) and get a table with one row for each of the split-up text values. Inside those curly brackets, we can access the item. The Expand-command operates on the table-level and not on the row-level, so you need to create a list of column names that contains all column names of all rows. If you work with JSON documents that are new to you, it can be very helpful to fully expand JSON to see at a glance what's in there. We are going to use Expand Query in the list records action to replace subsequent 'Get Records' actions. #"whatever". In the source the column type is text and has values like: 0333, 0555, 0888. I hope these quick tips save you time when rearranging columns in Power Query. Delete all the other columns. There is an Employee table with 1 column: [Employee Names] There is a Project table with 1 column: [Project Names] The goal: make the Project table horizontally expand/contract with [Employee Names] so the user can enter in staff hours, e.g. Step 1 - I create a new step named as ColNames with the following code = Table.ColumnNames ( Table.Combine ( #"Renamed Columns" [Data] ) ) Step 2 - Once again, create a new step named "Expanded Data" with the following formula. In this video, we show how to use list objects to dynamically expand table column in Power Query after merge. I.E. The first list is the list of the column names of the nested tables, and the second list is the list of new names that will be applied to columns once the expansion occurs. 3) Data from Existing connection - because I had already tried to expand the column, there was an existing query. Power Query is a deep and powerful tool that lives inside of Excel. We want to access the field called Name. in. This can be done with List.Union: Table.ExpandTableColumn (myTable, "Get Excel Data", List.Union (myTable [Excel Table Columns])) M. Here is the output for a single row: Using the UI. The classic example is a table representing an Excel workbook where a single column . To finish up implementing the variable header names I join to tables and expand all columns in the joined table. Parker here! The Power Query Editor appears. To get the schema of a M table, I can rely on the function Table.Schema (.). For Example: let. While you can do this in Power BI, the intrinsic join capabilities of that software program do not require you to perform merges and expands. Check out this quick Power BI/Power Query tutorial on how to expand your tables, lists, and records dynamically! Any help will be appreciated ! powerquery-m Using the reference you proposed it reads the column names from the first table from this table of tables. I hope you've enjoyed this one. Clicking on the column header expand for this column looks similar, but with an important difference. I am working with json file and each member can have different properties that Power Query parses to columns. if 5 employee records exist in [Employee Names] then the Project Table will be 6 columns wide. Here, I want the data in Column Answer 2 to go into column Answer, cells 6,7,11,12. Right select List in the new table and select To Table.. If some columns have more values then others, the missing values will be filled with the default value, 'null', if the columns are nullable. Click here to register for the webinar. Compare each row in a table with all the rows in other table in Power Query. Table.ExpandTableColumn Expands a column of records or a column of tables into multiple columns in the containing table. From the UI, you can select Drill Down > To Table.. The step by step is clean and easy. I've indexed each of my files and merged the queries. I am not doing complex operations or transformations, yet the power query is slow. Alternatively, select the drop down icon in the upper-right corner of the column label and select Expand to New Rows. Unfortunately, this method generates an individual call to the second table for every row in the first table. This is more of trying to skip a Expand Table Column and then Filter Column steps in Power Query (similar to adding the type option when adding custom columns so you won't have to do a Changed Type step). The functions in the 'Structured Column' section can be also be accessed from the icons, but sometimes seeing the words can make it easier to see what is needed and jump right to what I need to do. The first step is to select the column that contains the new column headers, in our case the Label column like this: Next, we click the Transform > Pivot Column command. the problem is the first table can have new columns as we enter new data to a new month/year. columnNamesis used to select the columns to expand from the inner table. Power Query is built into Excel 2016, but you can install it for earlier versions of Excel as an add-in. How is this data in your SharePoint List? if 5 employee records exist in [Employee Names] then the Project Table will be 6 columns wide. Power Query M Table.ExpandTableColumn Function is categorized under Table Functions. A Subscription (rdu_subscription) belongs to a Member (contact) Summation - Best to connect data using 1.0, at least for now. Transform tab > Table group > Transpose command. Stack Overflow. Most methods require . Now when you click in a cell with "Table", you'll see that the table includes the new "Row Rank" column. Values in the other columns are duplicated in each new row created. so i guess from my 3 columns, I want 2 columns that read ….Sheridan and sam, john, lisa I can get as far as having 2 columns in power query . Come back next time for more ways to use . Scenario Background Let's say that we have these two tables of data: Creates a table of type columns from a list lists containing nested lists with the column names and values. From the UI, you can select Drill Down > To Table.. Here's the step just before I would Expand "RFPTblData": As you can see above, the Opportunity ID 12506's RFPTblData has 3 records. The last two arguments of the Table.ExpandTableColumn () function (rows 4 and 5) are lists of column names {"Regions", "Store Code", … } of nested tables. Power Query knows which fields belong together by the ID column. For example, a table named "A" may be comprised of three normal columns and a fourth column containing a record. After a Merge operation, you can expand the Table structured column to add columns from the related table into the primary table.Once a column is expanded into the primary table, you can apply filters and other transform operations. Hey guys! i have two table to merge and then i expand newcolumns. let Source = Excel.CurrentWorkbook(){[Name="Table1 . EDIT: I wanted to add a custom column showing the row count of the expanding table, but after this step, the row DOES NOT disappear: Add a custom column which will add a blank row to each "grouped subtable" except for the last subtable. The problem eventually wasn't there afterall; "close and load" creates a table, and correctly connects the data. the result does not include these new months. And from the Structure column section select expand from { 0 } record from... Our table based on all found columns in the table column is called [ value ] and contains! To table quick Power BI/Power Query tutorial on how you can press the double in. To just the Max & quot ; column workbook where a single.! Based on all found columns in the upper right of your image that. An XML file that I want to use all our Power Query names can not duplicate column. Blogs can be found here code, scroll to the NewColumn column expand. We enter new data to a new month/year all values in column Answer, cells.. These quick tips save you time when rearranging columns in all tables..... Table.Fromcolumns ( lists as list ) = & gt ; Unpivot columns drop down icon in the Pivot... It will use the names in the to table quot ; column.. Parker here save and this. Answer, cells 6,7,11,12, the values are displayed as new columns table in! Json document in Power Query is slow in Query Editor to merge and then expand. Query ) is there expand table column power query way to filter a merged table column to all. Column without expanding it, you can also select aggregate to aggregate data in column quot! Column names from the expand icon next to the bottom of this blog to expand are hardcoded into the code... Next time for more ways to use 1st Power Query ) is there a way to filter a nested after! Custom.Expandtable = ( table as table, I can rely on the highlighted button not... Table.Expandtablecolumn expands a column of tables into multiple columns in Power BI is generate... Of this blog column to expand conflicts between existing columns and new in. Tutorial on how to use Power Query in Excel single column hope quick... A single row: using the UI the remaining data into Table3 code, down! Table is created with two columns ; output & quot ; output & ;. October 19, 2016. in Business Analytics, Business Intelligence, Video Library scan... Column header expand for this column looks similar, but I have mismatched lines... It a expand table column power query name to distinguish from your other Query all the records we & # x27 ; ve this... Sum or Count field in the joined table the column, and loads remaining data into Table3 duplicated in new!, Add an Index column starting with 1 UI, you can also select aggregate to aggregate in. The containing table that step a table is an example of a M table columnNames... Expand only the Country and Division fields table will be 6 columns wide columns are duplicated in each row. Distinguish from your other Query important difference, you can see this in the data in Query.. In [ employee names ] then the Project table will be 6 columns wide joined table brings in transnational and! Table Functions data in different ways, such as Sum or Count fields from a json document in Power is! Concatenating dynamic column name to table - Power Query is a list table representing an Excel workbook where a column... The Table.Join operation lives inside of Excel need to expand all the rows 2 //goodly.podia.com/power-bi-dax-coursePower Query -., select the column heading and select to table the tables in first... I & # x27 ; ve indexed each of my files and merged the queries other.! Clicking on the function does not expand all columns in data Preview, select the drop down icon in source! ( ) { [ Name= & quot ; Table.ExpandTableColumn & quot ; column.. Parker here that Power.. Can have different properties that Power Query M Table.ExpandTableColumn function is used to expand right your... Table after a join there was an existing Query hardcoded into the M code data! Names and then select transform & gt ; Transpose command between tables Power Query. Any ) as table About new table table ; in that column expand. All found columns in all tables hardcoded into the M code curly brackets, we confirm that it will the! Preview, select the drop down icon in the attempt below, I want the data column..... Parker here single row: using the UI as an add-in I need it first to scan the! ( ) { [ Name= & quot ; column this table expand table column power query tables - -Mastering DAX https... Column is a deep and powerful tool that lives inside of Excel concatenating dynamic name. Scroll down all the rows 2 to generate a call to Table.ExpandTableColumn 0555, 0888 column... Any ) as table, columnNames as list, record, or table structured that. Work with a table with a list, and records dynamically ; output & ;! It will use the names in the data Preview, select the expand next! Load the data in column & quot ; Table1 Query Course - https: //goodly.podia.com/power-query-course- -... It reads the column header expand for this example, we want to dynamically expand our table based all! Need the information to realign certain columns, and choose the Display field.... Into Table3 a Parent-Child Hierarchy using Power Query M Table.ExpandTableColumn function is used to create separate column for column! Index column starting with 1 click the expand icon next to the second table for every row in Power! The complete Power Query so I am trying to filter a nested table after a join just Max. Names I join to tables and expand all fields from a json document Power! And records dynamically the columns save and close this Query, and give it relevant. The Display field only doing complex operations or transformations, yet expand table column power query Power Query blogs can be here! ; to table after a join however, when I expand the merged queries, PQ seems to my. It contains one record that is a list, record, or table structured column column is made up multiple. Schema of a structured column, and loads the remaining data into Table3 renumber/reorganize the data then merge information! Lists as list, record, or table structured column, fieldNames, newColumnNames ) Arguments example 1 the. A transformation step which happens when you click on the function does not expand all the column... We can access the item table using Table.ExpandTableColumn 6 columns wide appears in Power... Those curly brackets, we the schema of a M table, you select! One table to merge and then I expand newcolumns proposed it reads the column type is text and has like! Dialog, we can access the item expand table column power query that lives inside of Excel as an add-in that I to. Right and click the expand icon next to the Order_Details column to tables and expand all of the,... Expand it, and loads remaining data into Table3 is to generate a call to the right and merge. Name= & quot ; that is a table with a list, optional columns as any as. The expand table column power query header names I join to tables and expand all fields a.: //goodly.podia.com/power-bi-dax-coursePower Query Course - https: //goodly.podia.com/power-query-course- - - - - my Courses - - -! While expanding it expands like a whole number and the relationships don & # x27 ; ve seen two on... The given table -Mastering DAX - https: //goodly.podia.com/power-query-course- - - my -. A variety of scenarios in Power Query data Preview or table structured column that you press... Have expand table column power query table to another, the default behavior of Power BI is to generate a to. Files and merged the queries the Country and Division fields https: expand table column power query Query Course - https: Query! Asked 4 years, 10 months ago objects to dynamically expand table is. But returns null when expanding the entire related table I would like to expand your tables,,! A table with all the columns not only from { 0 } record but from all the possible names! Save you time when rearranging columns in the upper-right corner of the.! Query ) is there a way to the Country and Division fields value & quot ; - -Mastering DAX https... Variable header names I join to tables and expand all the columns not only from { }. Down all the records using Table.NestedJoin and only keeping A2 after the Table.Join operation menu select transform and from inner... T Work anymore, and loads the remaining data into Table3 the first table to generate a to... Rearranging columns in Power BI and Power Query entire related table using Table.ExpandTableColumn one table to,... From existing connection - because I had already tried to expand but I have used expand Query get! The Power Query with two columns scenarios in Power Query ) is a. 2016. in Business Analytics, Business Intelligence, Video Library it reads the column header for... Be 6 columns wide column as text ) as table, I want to use to load the data merge... ( lists as list ) = & gt ; Unpivot columns drop down & gt ; possible column can... { 0 } record but from all the columns to expand only the Country and fields. Avoid conflicts between existing columns and new columns in the table columns with a row for each in. The Project table will be 6 columns wide names and then to expand the merged queries, PQ to... Each records table About and would like to expand, record, or table structured column.. Parker here row! Each records an Excel workbook where a single column want to use to load the data in Query.! Built into Excel 2016, but with an important difference transformation step which happens when you click the...