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

Thanks for helping,
Jacek

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

6 Replies

  • jaryszek's avatar
    jaryszek
    Super User
    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

    • jaryszek's avatar
      jaryszek
      Super User
      1 year ago

      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

  • 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