Power Query / M language: How to combine multiple columns per ID into one record

kbarber

Please try this

Please see attached excel file as well

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Emp", Int64.Type}, {"Address", type text}, {"City", type text}, {"State or Prov", type text}, {"Postal Code", type any}, {"County", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Emp"}, {
                                                      {"Address", each List.RemoveNulls([Address]){0}}, 
                                                      {"City", each List.RemoveNulls([City]){0}}, 
                                                      {"State or Prov", each List.RemoveNulls([State or Prov]){0}}, 
                                                      {"Postal Code", each List.RemoveNulls([Postal Code]){0}}, 
                                                      {"County", each List.RemoveNulls([County]){0}}
                                                             }),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Grouped Rows", {{"Emp", null}, {"Address", null}, {"City", null}, {"State or Prov", null}, {"Postal Code", null}, {"County", null}})
in
    #"Replaced Errors"