Use a query table to initialize other query tables

[V-lianl-msft]

Scenario: 

Sometimes you may need to handle multiple data source with different table structures ,and you only need to use some of fields for analysis.  

Currently, power query seems. Therefore, if you have to deal with huge amount of sources and have to manually process with structure initial, that will be a problem.

Expressions: 

Custom function: recognize and return table structure(can be used in format steps) based on a sample table. 

Let 

    RecognizeStructure=(Source as table) =>  

    List.Zip({Table.Schema(Source)[Name],List.Transform(Table.Schema(Source)[Kind], each Expression.Evaluate("type "&_))}) 

in 

    RecognizeStructure 

invoke result:  

It returns a structure of 'column name' and 'data types' that can be used in 'change type'. steps. 

Expanded structure: a structure list of column 'Name' and 'Type'. 

Usage: 

For the default operation step, if you want to change the table structures, you need to right click to choose specific column fields to keep or delete, and you need to check current 'change type' steps to confirm your table field data types have been recognized correctly.   

It should be heavy workload if you have to deal with huge amount of query tables.  

For example:  

You are designing a report but your datasource are stored on different type of databases(e.g. excel, sql, rest api and other types), these database may stores your records with different structures(some of them has additional fields that you not needed). The following steps can help you to reduce the 'change type' operations.  

1. Invoke custom function with existing "template" table. 

RecognizeStructure(Template) 

1. Modify the new table 'change type' step to invoke and format based on 'template' table. 

Raw 'change type' steps: 

Table.TransformColumnTypes(Table,{{'ColumnName1', 'data type'}, {'ColumnName2', 'data type'},...})  

Replace your 'change type' steps parameters: 

#"Changed Type" = Table.TransformColumnTypes(Table.SelectColumns(Source,Table.ColumnNames(Template)),RecognizeStructure(Template)) 

1. Duplicate 'step 2' operation code to other query tables 'change type ' step that you need to format and they will be formatted based on the template table structure.  

1. Turn off the 'enable loading' of the template table to prevent it from being displayed on report view side. 

Comments of function and parameters: 

Table.SelectColumns(Source,Table.ColumnNames(Table)) : read table fields from template table. 

RecognizeStructure(Template) : use custom function to extract table structure(field name, and type) for further format operations. 

Notice: 

This blog should suit the scenario you are working in, or try to collect the table records stored in different databases or try to collection the tables records that stored in different databases. 

Author: Xiaoxin Sheng

Reviewer: Yuyang & Ula 

formatting based on template table (1).pbix