Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kbarber
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
Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

View solution in original post

2 REPLIES 2
Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

thank you @Zubair_Muhammad!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.