Pivoting Unstandardized Data

Power Query
1

Hello,

I’m wondering if there is an easy way to clean up this data set.

I attached an excel file with the before and after modifications. It seems like it should just be a clean pivot, but some records are missing data for both the name and the car make.

Records

Missing Car Makes
Missing Car Makes896×409 39.6 KB
Missing Names
Missing Names895×290 25.8 KB

Any ideas on a systemic way to transform the data? I tried to add an index, but that doesn’t help much.
When I try to pivot it give me an error “There were too many elements in the enumeration to complete the operation”.

Sample Data.xlsx (123.1 KB) Pivoting Unstandardized Data.pbix (141.2 KB)

2

@ibesmond,

I think with a pivot and then a Group By it gets you what you’re looking for:

image
image1631×544 53.6 KB

let
    Source = Excel.Workbook(File.Contents("C:\Users\brjul\Downloads\Sample Data.xlsx"), null, true),
    #"Original Data_Sheet" = Source{[Item="Original Data",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"Original Data_Sheet",{{"Column1", type text}, {"Column2", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[Column1]), "Column1", "Column2"),
    #"Filled Down" = Table.FillDown(#"Pivoted Column",{"Business Name"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Business Name"}, {{"MaxName", each List.Max([Name]), type nullable text}, {"MaxCar", each List.Max([Car Make]), type nullable text}, {"MaxLoc", each List.Max([Location]), type nullable text}})
in
    #"Grouped Rows"

It’s then a simple matter to Split by delimiters if you want to break up location into separate fields:

image

I hope this is helpful. Full solution file posted below.

– Brian
Pivoting Unstandardized Data Solution.pbix (86.0 KB)

2 Likes
3

Thank you for a solution. I had been experimenting with the group-by, but on the front end before I did any pivoting. I now understand why you need the unique index rather than a repetitive one. The fill down was another major step I was missing. Cheers!

4

@ibesmondm

Great – glad to hear that was helpful. FYI – keep your eye on the Enterprise DNA TV YouTube channel. I’ve got a video coming out next week dealing with GROUP BY and ALL ROWS that I think you’ll find relevant and hopefully interesting.

– Brian

2 Likes