none
How to change multiple columns data types in power query RRS feed

  • Question

  • Hi team,

    I just have a situation like this:

    I have a table like this and all the of columns are set to text as default.

    I want to change the data type as: text, date, true/false and whole number for the 4 columns listed above.

    Of course, I can manually to change them like the following query:

    Table.TransformColumnTypes

    (

    tb_Table,

    {{"Product", type text}, {"Date", type date}, {"Scan", type logical}, {"Value", Int64.Type}}

    )

    The long list of lists are the key part to this issue.

    While I want to make it work dynamically, which based on the information given in a separate table to do this more dynamically.

    So I create a reference table named as dttype as below:

    The first column is the column name and the second column is the data type I am looking for.

    I create the following query:

    In this query I use List.Transform function to build a list of list based on the information from reference table and want to change data types correspondingly.

    Step lt1 is a list of lists and to build the list of lists which mentioned before while this doesn't work and return the following error:

    However, if I change all of them to type text and hard code this, it works and I can change all the columns to text.

    Could you please tell me how to change type text to type Type and what is wrong with the query I build?

    Kind regards,


    Tom Sun


    • Edited by Tom J Sun Thursday, March 15, 2018 9:14 AM
    Thursday, March 15, 2018 9:13 AM

Answers

  • After I few searches, I got the solution for this issue   :)

    This is a useful post to this issue:

    https://social.technet.microsoft.com/Forums/en-US/5883f1cd-0665-45b5-92bf-ccf3489d37c7/transform-column-types-dynamically-via-pq?forum=powerquery

    Let us have a look at the way to fix this issue

    The key here is to use function Value.Type to reference the data type you want to build and change the reference table as below:

    In the main query, to change the query as below:

    let
        Source = Excel.Workbook(File.Contents("C:\Users\Tom\Desktop\ttttttt.xlsx"), null, true),
        tb_Table = Source{[Item="tb",Kind="Table"]}[Data],
        lt=Table.ColumnNames(tb_Table),
        lt1=List.Transform
        (
        {0..List.Count(lt)-1},
        each
        {
        lt{_},Value.Type(tp[Data Type]{_})
        }
        ),

        ttt=Table.TransformColumnTypes(tb_Table,lt1)
    in
        ttt

    By doing this, the issue with reference type of data source can be resolved.

    Good to know...

    • Marked as answer by Tom J Sun Thursday, March 15, 2018 9:35 AM
    Thursday, March 15, 2018 9:35 AM