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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
AlvinLy
Helper II
Helper II

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Kudoed Authors