Add data types to all columns dynamically – part 2

Happy New Year! Welcome back 🙂 It’s been a while since the last post. Long break was forced mainly due to the fact, that I had to find a “new home” for my website. However, good news is that new hosting should perform a lot better, and now I am back on schedule.

As a first post in 2024, I would like to quickly wrap up a Power Query series, covered in two latest posts:

In first mentioned article, we are covering a custom function, which assigns a text data type to all columns in a table dynamically. There was actually a follow up request after this article, if we could force this function to ignore columns, where data type is already set. I am really happy to receive this very first user-request, that is why we will continue to have fun with Power Query today 🙂

We could use some sample data to copy for you as well, so you can follow along. Please, copy below code and paste it as a Source step in your Excel/Power BI:

let
    Source = Table.FromRecords({
    [OrderID = 1, Customer = "Bob", Product = "Square Oranges", OrderDate = "2023-12-06"],
    [OrderID = 2, Customer = "Jim", Product = "Straight Bananas", OrderDate = "2023-12-08"],
    [OrderID = 3, Customer = "Paul", Product = "Bald Coconuts", OrderDate = "2023-12-12"]
    }),
    
    // Change type for OrderID column. The rest we want to tackle dynamically.
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"OrderID", Int64.Type}})
in
    #"Changed Type"

Explain the request

Idea here is to set a type for a column OrderID and OrderDate, and whatever we have left should be a subject for our custom function, to assign text data type automatically. We could actually do it very quickly, use our custom function first, and then overwrite some of the columns with different data types. Personally, this is exactly what I would do :D, but we are here to have some fun with Power Query, so let’s complicate things a bit, and let’s try to make our function a bit more intelligent.

We already started to do it, as we improved our custom function to detect “Date” related columns, and assign “type date” to them instead of text. That is why we must set the data type manually only for OrderID column, and let’s set it to Int64.Type. Now, let’s have a look at the function we already created:

(sourceTable as table) as list =>                   
let 
    transformationList = List.Transform(    
        Table.ColumnNames(sourceTable),  // This is the part we are going to update  
        each
            // 1. We take each column name represented by "_"
            // 2. Check if that name contains phrase "Date"
            // 3. Assign data type accordingly
            if Text.Contains(_, "Date") then {_, type date}
            else {_, type text}             
    )
in
    transformationList

As highlighted in the code, we are going to modify the first step of custom function, which collects the names of columns that we are going to transform. In this moment, it will return all the columns names in our table.

At first, I thought this is going to be an easy task, as I remembered about very similar function to the one we are using: Table.ColumnsOfType. Please find the original definition below:

Table.ColumnsOfType(table as table, listOfTypes as list) as list

It takes a table as an initial parameter, but it also asks about the list of data types, related to columns we want to return. Let’s start building our custom function, right-click on our query and select “Reference”. Name new query as FindColumnsToTransform, this is going to be our function name. We will now use Table.ColumnsOfType function, that should return all columns of “type any”.

Please note, that Source step is related to our referenced Query, where we created our test data, and assigned numeric data type to OrderID column.

Function is simple, but… it doesn’t work. It returned all the columns, not only those with “type any”. It turns out, that this is the behavior of this function, and it returns any column, instead of columns of “type any”. If we would like to return numeric columns, just to check if this function works at all, we could do the following:

Maybe you noticed that we asked to return “type number” instead of “Int64.Type”. Challenge here is that we assigned data type to OrderID column through UI, which results in assigning “nullable” data type. That’s why in Table.ColumnsOfType function we must use “nullable” type as well. We can’t spend too much time explaining this behavior, but the most important thing for you to remember here is that asking for “type nullable number” will return all numeric columns you have in your table, regardless of the exact data type (Number.Type, Int64.Type, Currency.Type).

Now we know that Table.ColumnsOfType function works, but it is not useful to us in a scenario, where we are looking for “type any” columns. When I hit the wall like this one, I typically go to M language reference documentation from Microsoft, and I try to find the function that will solve my problem. Maybe there is an easier way to do it, but here is a function that I found: Table.Schema. It asks only for one parameter – a table to work on:

After calling this function, we will get list of all the columns in the table, together with lots of useful information. Here for example, we can see, that our OrderID type is In64.Type, but it belongs to “number” kind and is nullable. So, I was not lying about it 🙂

From this moment, we could work on this schema, as we would do for any other table. We could filter only rows related to “Any.Type”, and drill down on column [Name], and… that’s it 🙂 To drill down on a column, right click on Column Header, and select “Drill down” option at the bottom of the list. Here is the result you should get:

Now it works, we got the list of columns with unresolved data types, that we can pass to the function we created in earlier article. Instead of making one long function that will find the columns and assign the data types, we can transform our FindColumnsToTransform query into function, and pass it to the earlier function instead of Table.ColumnNames(…) part.

Transform query to function

At this stage, FindColumnsToTransform query should look as show below:

let
    Source = TableToTransform,
    #"Table Schema" = Table.Schema(Source),
    #"Filter Any.Type" = Table.SelectRows(#"Table Schema", each ([TypeName] = "Any.Type")),
    #"Drill down to Name" = #"Filter Any.Type"[Name]
in
    #"Drill down to Name"

To transform it into a custom function, we must update the code to look like this:

// Function named "FindColumnsToTransform"
(sourceTable as table) as list =>
let
    Source = sourceTable,
    #"Table Schema" = Table.Schema(Source),
    #"Filter Any.Type" = Table.SelectRows(#"Table Schema", each ([TypeName] = "Any.Type")),
    #"Drill down to Name" = #"Filter Any.Type"[Name]
in
    #"Drill down to Name"

Now, we will invoke our new function inside the previous one, and use it to return columns names for data type transformation:

// Final function is named "AssignDataTypes"
(sourceTable as table) =>                   
let 
    transformationList = List.Transform(    
        FindColumnsToTransform(sourceTable),  // This is the modified part of our function
        each
            // 1. We take each column name represented by "_"
            // 2. Check if that name contains phrase "Date"
            // 3. Assign data type accordingly
            if Text.Contains(_, "Date") then {_, type date}
            else {_, type text}             
    )
in
    transformationList

Test updated AssignDataTypes function

Since we have our function updated, let’s go back to the initial query, where we imported sample rows of data, and assigned data type to OrderID column. We will invoke our new function:

Our function works. OrderID column still has Int64.Type assigned, and remaining columns are transformed as expected, including OrderDate being properly recognized as Date column.

Conclusion

We couldn’t find a standard function to return the columns with unresolved data types, but thanks to existing Microsoft documentation we managed to find something that allowed us to build our own function. I wish this solution is at least half that useful, as it was fun to make.

This type of challenges may not always bring some tangible and re-usable results, but for sure will help you deep dive into Power Query, understand it better, and learn something interesting that might prove useful to you in future.

As always, thank you for reading, and till next time 🙂

Pawel Wrona

Lead author and founder of the blog | Works as a Power BI Architect in global company | Passionate about Power BI and Microsoft Tech

Did you enjoy this article? Share with others :)

5 4 votes

Article Rating

Comments notifications

0 Comments

Oldest

Newest Most Voted

Inline Feedbacks

View all comments