Forum Discussion

jaryszek's avatar
jaryszek
Super User
1 year ago
Solved

Importing table with predefined data type from metadata

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 ...
Need Help
  • jaryszek's avatar
    jaryszek
    1 year ago

    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.html 

    Best,
    Jacek

v-nmadadi-msft's avatar
v-nmadadi-msft
Community Support
1 year ago

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's avatar
jaryszek
Super User
1 year ago

Thank you very much, 

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

Best,
Jacek