Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AlvinLy
Helper I
Helper I

Combine columns with different data types

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:

ActivityIDCodeTypeValueNumberValueTextValueDate
1Total43nullnull
1SizenullSmallnull
1OrderDatenullnull2024-06-01
2SizenullLargenull
2OrderDatenullnull2024-06-06
3OrderDatenullnull2024-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:

 

ActivityIDTotalSizeOrderDate
143Small2024-06-01
2nullLarge2024-06-06
3nullnull2024-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?

1 ACCEPTED SOLUTION

 

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

 

View solution in original post

4 REPLIES 4
lbendlin
Super User
Super User

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.

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,

 

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

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors