Forum Discussion

AlvinLy's avatar
AlvinLy
Helper II
2 years ago
Solved

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: ActivityID CodeType ValueNumber ValueTe...
Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago

     

    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

     

AlvinLy's avatar
AlvinLy
Helper II
2 years ago

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,

AlienSx's avatar
AlienSx
Super User
2 years ago

 

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