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

     

lbendlin's avatar
lbendlin
Super User
2 years ago
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.

  • 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,

    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago

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

    • 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