cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
kbarber Frequent Visitor
Frequent Visitor

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: 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"
2 REPLIES 2
Super User
Super User

Re: 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"
kbarber Frequent Visitor
Frequent Visitor

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

thank you @Zubair_Muhammad!