Combine columns with different data types

[Syndicate_Admin]

Hello,

I have a scenario that i'm not sure if it's possible or not. I have a table with an identifier, codetype, and three value columns. See below:

ActivityID	CodeType	ValueNumber	ValueText	ValueDate
1	Total	43	null	null
1	Size	null	Small	null
1	OrderDate	null	null	2024-06-01
2	Size	null	Large	null
2	OrderDate	null	null	2024-06-06
3	OrderDate	null	null	2024-06-12

The column ValueNumber stores the data as type number, ValueText as type text, and ValueDate as type date. In each row one of the three values will have a value and the other two will be blank/null. I want to combine the three columns and then pivot table (Table.Pivot) so it looks like this:

ActivityID	Total	Size	OrderDate
1	43	Small	2024-06-01
2	null	Large	2024-06-06
3	null	null	2024-06-12

Is there a way to have the "Total" column remain as data type number, "Size" as data type text, and "OrderDate" as data type date? For background information, my dataset is much larger as this is a small example; my actual data has 20+ codetypes and thousands of rows. I want an automated way of changing the data type prior to the data showing up in PowerBI. The reason I need the datatypes is because my PowerBI may use measures that requires comparisons or calculations for columns that are data type text or date.

Other than manually changing data type in PowerBI or Power Query, is there a way to maintain data type after combining columns and pivoting the table?

[Syndicate_Admin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrJL0nMAdImxkAirzQnB0bF6kAUBGdWpSKkgnMTMZX4F6WkFrkklqSiGaGjZGRgZKJrYKZrYAhWbIRhnk9iUXoqsnlGxJlnBlZsTJRiQyOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ActivityID = _t, CodeType = _t, ValueNumber = _t, ValueText = _t, ValueDate = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ActivityID", "CodeType"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "null")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"ActivityID", "CodeType", "Value"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[CodeType]), "CodeType", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Total", Int64.Type}, {"OrderDate", type date}})
in
    #"Changed Type"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[Syndicate_Admin]

Hi Ibendlin,

I don't want to explicitly change the column type as a step as I want this to be a robust solution. If I have 20+ codetype that can change based on the project, then I will need to alter the code every other day

Thanks,

[Syndicate_Admin]

You can have a mapping table that indicates the desired column type for each column name. For example "ValueText"  would map to "type text".

[Syndicate_Admin]

let
    Source = your_table,
    merge = Table.CombineColumns(Source, {"ValueNumber", "ValueText", "ValueDate"}, (x) => List.RemoveNulls(x){0}, "x"),
    types = List.Zip(Table.ToColumns(Table.Group(merge, "CodeType", {"type", (x) => Value.Type(List.First(x[x]))}))),
    pvt = Table.Pivot(merge, List.Distinct(merge[CodeType]), "CodeType", "x"), 
    col_types = Table.TransformColumnTypes(pvt, types)
in
    col_types