Unique Values from Column 1 convert Column 2 into Rows

[Craigc3814]

I have transformed my data as far as I can get and probably as inefficiently as possible but I am so close to what I want.

In column 1 (Address) I have a list of all address' in column 2 (Activity) I have a list of all activities that happened at that address and column 3 (Count.1) I just added to try and pivot the data it isn't necessary other wise it just counts the distinct amount of activities at a given address.

So when I group I get the same address in every row for each activity that took place. So if 1 address had 15 Activities it creates 15 rows. What I want it to do is give me 1 row for each address and a column for each Activity.

If I pivot the data it creates tons of rows because there are a ton of activity codes, most of them are null. Is there a way I can just create columns for the activities that actually happened at an address?

Below is my very choppy code.


let
Source = Table.NestedJoin(#"Time Keeping",{"Work Order .1"},#"Work Orders",{"Work Order Number"},"NewColumn",JoinKind.Inner),
#"Expanded NewColumn1" = Table.ExpandTableColumn(Source, "NewColumn", {"Work Order Number", "Activity", "Asset Type", "STNO", "PreDir", "Address", "Suffix", "Zip Code", "Map #", "Assigned To", "Initiated", "Scheduled Start", "Scheduled Finish", "Due", "Closed", "Maintenance Type", "Priority", "Problem", "Sub Area", "Responsibility", "Service Request #"}, {"Work Order Number", "Activity", "Asset Type", "STNO", "PreDir", "Address", "Suffix", "Zip Code", "Map #", "Assigned To", "Initiated", "Scheduled Start", "Scheduled Finish", "Due", "Closed", "Maintenance Type", "Priority", "Problem", "Sub Area", "Responsibility", "Service Request #"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded NewColumn1",{{"Date", type date}, {"Initiated", type date}, {"Scheduled Start", type date}, {"Scheduled Finish", type date}, {"Due", type date}, {"Closed", type date}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"STNO", type text}}, "en-US"),{"STNO", "PreDir", "Address", "Suffix"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Merged", "Address"}}),
#"Cleaned Text" = Table.TransformColumns(#"Renamed Columns",{{"Address", Text.Clean}}),
#"Trimmed Text" = Table.TransformColumns(#"Cleaned Text",{{"Address", Text.Trim}}),
#"Added Custom" = Table.AddColumn(#"Trimmed Text", "Open or Closed?", each if [Closed] = null then "Open" else "Closed"),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Hours", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"Address", "Activity"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type number}}),
#"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Address"}, {{"Count", each _, type table}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows1", "Count", {"Activity", "Count"}, {"Activity", "Count.1"})
in
#"Expanded Count"

 

[Matt Allington]

I'm not sure I fully understand, but when you Pivot data, a row will be created/kept if there is at least 1 column that has a value. If you have 100 columns and only 1 has a value, the other 99 will be null. Conceptually, what else are you expecting?