Finally, we are going to do some fun stuff here 🙂 I recently took a part in a October challenge for Power Query, you may find it here. We will not go through the content of that challenge yet, as there is still time to submit your files by the end of October. I will talk about it in details closer to the end of the month, but for today, I wanted to tackle a specific problem from this challenge.
Main problem of the challenge
As you may find out, the challenge isn’t that hard in its base problem. Complexity is added when author mentions to try make it immune to column changes. Specifically, he would like this solution to work on all current future columns, that might be added to the source table. For us it means, that whatever we do in Power Query, we must avoid calling specific columns by their names. Of course, we will talk about the solution for entire challenge in next article, but we will try to solve today the problem of fixing column data types in a dynamic way.
How it’s done with UI
Power Query allows us to complete many operations, without writing single line of code. Transforming column types is one of these operations. All we need to do is to select a column choose the data type. There is actually at least three ways of doing it using UI.
Table.TransformColumnTypes(
Source,
{
{"Client ID#", type text},
{"System 1 contact ID", type text},
{"System 2 contact ID", type text},
{"System 3 contact ID", type text},
{"Name", type text},
{"Email", type text},
{"Phone number", type text},
{"Deal ID#s", type text}
}
),I formatted the code so it’s easier to understand what we need to achieve. It’s also important to mention, that we didn’t have to write this code, it was automatically generated while using UI. First line is a function responsible for transforming column types. Second line is the first argument which this function needs, so the table to work on. In our case, we are referencing the Source step, where table is defined. Second argument is simply the list of transformations, that happen for each column. Each transformation is of a list type as well, we can see it because of the curly brackets surrounding each argument.
Since we know that this is the place where column names are mentioned, this is going to be the part of code that we must create in a dynamic way. Our end result must be a list of lists, containing pairs of Column name and type of the data, which we want to assign to that column. Let’s remove this step from Power Query and we will start over. First function we will call is Table.ColumnNames:
This function needs only one argument – a table containing the columns we want to list. Here again we are referencing the Source step, where the table is defined. As a result, you see the List structure, containing all the column names from our table. With this one function we are almost there. We must create a list of lists, so far, we have a list of values, but at least we managed to call out all the column names, without mentioning them in the code!
How to create a list of lists in this case? We must Transform our current list, to wrap each item with curly brackets “{}”. This will assign given item to the list. Let’s try and use the following function:
List.Transform( // Function we need to call to transform the list
Table.ColumnNames(Source), // We take our list with Column Names as an argument
each {_} // Item of the list is represented by "_"
)We get the following output:
As we can see, we converted our list of values to list of lists, containing column names. But we need to two elements in each list, a column name, which we already have, and data type. We can add second element to list we created with curly brackets like this:
List.Transform( // Function we need to call to transform the list
Table.ColumnNames(Source), // We take our list with Column Names as an argument
each {_, type text} // Item of the list is represented by "_"
)What we did now was very simple, only “type text” was added as a second element to the list we create from each column name. How could we make a good use of it for any future case? We can turn it into a Custom Function:
(sourceTable as table) => // Our function expects table to be passed as an argument
let
transformationList = List.Transform( // Function to Transform list of column names
Table.ColumnNames(sourceTable), // List of columns names
each {_, type text} // Combine column name with data type
)
in
transformationList // Return the resultNow, coming back to the first code snippet we have on the top, where we see function assigning data types to each column, we must replace 2nd argument by invoking our custom function. If my function is named “AssignDataTypes”, code step looks as follows:
Instead of having the list of lists, with transformations for ech column, we have our custom function, that calls the names dynamically.
What about other data types?
Well, this is a good question. We could approach it in two ways (probably more than two, but we will cover two of them only :)). First would be to try detecting columns by name pattern, and assign different data types to them, or we could assign some of them manually, and tweak our function to change the type only for the remaining columns. Second approach turned out to be a bit more complex, so I will describe in another article. Let’s have a look at first scenario, detecting columns by naming patterns.
This could be easily explained with the date columns, as we could expect to find column names that contain “Date”. In this case, we could do the following:
(sourceTable as table) =>
let
transformationList = List.Transform(
Table.ColumnNames(sourceTable),
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
transformationListInstead of assigning text type everywhere, as I added an “if” statement to find columns, that might contain “Date” in the name, and assign “type date” instead. The result is the following:
Last column has been correctly classified as Date column, and the rest we see appearing as Text columns.
Conclusion
We managed not only to build a solution, dynamically assigned column types in our table, but we even extended the functionality to assign different data types, based on name pattern detection. I hope you find this solution useful. If you have any questions, let me know in the comments section.
Thank you for reading, and till next time 🙂
