Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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"
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"
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |