A common issue that I have faced consistenly is that when expanding columns that contains tables in Power Query the data types of the new columns are not retained and due to which I have to add another step that hard codes the names of the columns and the data type.
In this blog I just want to show how to not make the same mistakes I have made and prevent adding redundant steps to the query. You can download the file from here:
First an example of what I mean, I have 2 tables Products and Sales
I want to add columns of Sales to Products to compute the Sales Amount per Product, and I did that with the merge queries option,
And then I added a new column to that table in each row which computes the Sales Amount row by row.
If I expand any one of the table you can see the data types of the columns are correct.
But if I expand the whole column and select only SalesAmount column then the data type information about that column is lost:
Finally to fix that data type I will have to add another step, but is there something we can do? Absolutely, let's see how:
So in the step where I created the new column for the Sales Amount, I can actually pass the data types of each columns. We need to pass a table that will scpecify the Name of the original column as well as the type
And now if I expand the column the data types will be retained:
Not only this, by specifying the the table you can actually limit how many columns are visible while expanding, see below. All columns are visible if they are specified in the table construct:
Single column SalesAmount is visible as only one column is specified:
And now we don't need to manually change the data types of the columns.
Now let's take another example of where the same issue happens after Grouping a table.
Let's say we have a list of course and the dates on which they were conducted and we want to retain the same number of rows of the original table but add a new column that shows the First Date of each course repeated for each row.
And for that we can use the Group By option to first create groups. Pay attention the the M code here, in the grouping step, Power Query tries to indicate and remember the data types of the original columns.
And me being me used to remove that table part completely because I felt that it was useless, until I figured my mistake, and to replicate that I have removed the highlighted part and inserted a new column to get the First Date.
Just like previous example, if I expand individual row then the data type is intact:
But if I expand the whole column then the information is lost:
And to fix this I would add a new step to change data type, but now that we know how we can fix this we can use the table construct in the group by step.
Finally the expanding the whole column shows the correct data type:
So moral of the story is don't ignore the automatic data type table created by the group by step and in case if you are going to expand a column containing tables then make sure the previous step that added the table specifies the data types of each column using the type table construct.
Comments