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

[kbarber]

I created an example file of my issue. The first sheet shows the initial shape, the second sheet shows the desired result.  Need to see the m code to get from sheet 1 to sheet 2.

I'm struggling to properly articulate the transformation needed.  The subject of this post is the best I can do.

Thank you in advance!

- Kurt

[Zubair_Muhammad · Accepted solution]

@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"

[kbarber]

thank you @Zubair_Muhammad!