Importing table with predefined data type from metadata

[jaryszek]

Hi Guys,

I do not want Power Query to automatically detect data changes. 
If it is possible to read column data types from medata table like:

Table1, Column1, String
Table1, Column2, Integer

Thanks for helping,
Jacek

[jaryszek · Accepted solution]

Solution code:

let
    types = [Text = Text.From, #"Decimal number" = Number.From, #"Whole number" = Int64.From],
    type_table = Table.Group(
        Excel.CurrentWorkbook(){[Name="ColumnDataTypes"]}[Content], 
        "TableName", 
        {
            "transform", 
            (x) => List.Zip(
                {
                    x[ColumnName], 
                    List.Transform(x[ColumnDataType], (w) => Record.FieldOrDefault(types, w, each _))
                }
            )
        }
    ),
    type_record = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(type_table))),
    result = ((table_name) => Table.TransformColumns(
        Record.Field(#shared, table_name), 
        Record.Field(type_record, table_name), 
        null, 
        MissingField.Ignore
    )) ("PetTable")
in
    result

approach taken from https://www.excelforum.com/office-365/1433993-power-query-to-import-column-data-types-from-table.htm... 

Best,
Jacek

[v-nmadadi-msft]

Hi @jaryszek,
Thanks for reaching out to the Microsoft fabric community forum.
First, disable the automatic data type detection in Power Query. To do this, navigate to Options and Settings, then select Options.

In the Global Data Load settings, select the specified option from the screenshot to disable Power Query from automatically detecting data types. Alternatively, if you want to apply this setting only to a specific file instead of globally, you can configure the same option in the Current File settings. This ensures that automatic data type detection is disabled only for the selected file while keeping the global settings unchanged.

After completing this step, we can configure the M query to dynamically read the metadata and apply the corresponding data types accordingly.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[jaryszek]

Thank you very much, 

I went with the settings and I am ready to write power query M code to read metadata.

Best,
Jacek

[v-nmadadi-msft]

Hi  @jaryszek,

Please create a function in Power Query to map data types and then using Table.TransformColumnTypes to manually assign the appropriate data types to your dataset for better control and accuracy.

Reference documents:
Using custom functions in Power Query - Power Query | Microsoft Learn
Table.TransformColumnTypes - PowerQuery M | Microsoft Learn

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[jgeddes]

It will depend on the source of your data.
Please see the attached support document.
https://support.microsoft.com/en-us/office/add-or-change-data-types-power-query 

[jaryszek]

Thanks but i do not want to make power query detecs data types from my source.

I need to have a table with metadata and apply it for each power query to read data types.

How to make a power query to achive it?

Best,
Jacek