Restructure table with multiple attribute value columns into single rows per selected attribute

[ct_tm]

I have a database table that was built to allow up to 5 selections of attributes (with no data controls on the first selection meaning it can be null with a non null data value associated, common_data_ids 9404-9406 are examples of this). Also, the frontend image and the data are not in the same order. The key is Focus Area and each one has 3 other associated values.

In the data, each Focus Area is labeled as focusselection_1 through 5 with matching numeric values for the Service Name (focusselection_1_details), # Times Occured (focusselection_1_occurances), # Residents Participating (focusselection_1_participating).

In the attached excel dataset I have been trying to use indexes, pivot/unpivot, and grouping to try to do what I need to do but I end up creating duplicate lines with no real way to remove the correct one.

The desired result would be to have a line PER Focus Area.

If we use the item in the image above, common_data_id 19305 I would want two rows printed.

The first row would be the associated data for Church luncheon (the 4 columns named focusselection_1_*)

The second row would be the associated data for Bingo (the 4 columns with named focusselection_2_*)

There would be no more rows because the reast of the data is blank for the remaining 12 (3 blocks of 4 values) columns.

The common_data_id would be the same between the two rows but the data within the columns would be different.

Can someone take a stab at helping me reorganize this?
I swear I used to be able to upload files, but here is a public link to my OneDrive for the data file, hopefully this is accessible.

Resident Services.xlsx 

Thanks!

[collinsg · Accepted solution]

Good day @ct_tm 

I have a method which gives the output below. If you are expecting a different output, I sorry, I haven't understood the question properly.

Here is the query I applied to your data (I first put your data into an Excel table which I called Table1 - click in the data and CTRL-T to create a table from a range of data).

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"common_data_id", Int64.Type}, {"focusselection_1", type text}, {"focusselection_1_details", type text}, {"focusselection_1_occurances", Int64.Type}, {"focusselection_1_participating", Int64.Type}, {"focusselection_2", type text}, {"focusselection_2_details", type text}, {"focusselection_2_occurances", Int64.Type}, {"focusselection_2_participating", Int64.Type}, {"focusselection_3", type text}, {"focusselection_3_details", type text}, {"focusselection_3_occurances", Int64.Type}, {"focusselection_3_participating", Int64.Type}, {"focusselection_4", type text}, {"focusselection_4_details", type text}, {"focusselection_4_occurances", Int64.Type}, {"focusselection_4_participating", Int64.Type}, {"focusselection_5", type text}, {"focusselection_5_details", type text}, {"focusselection_5_occurances", Int64.Type}, {"focusselection_5_participating", Int64.Type}}),
Unpivoted = Table.UnpivotOtherColumns(#"Changed Type", {"common_data_id"}, "Attribute", "Value"),
#"Split attribute names" = Table.SplitColumn(Unpivoted, "Attribute", Splitter.SplitTextByPositions({0, 16}, false), {"Focus Selection", "Attribute.2"}),
#"Filtered Rows" = Table.SelectRows(#"Split attribute names", each ([Focus Selection] = "focusselection_1" or [Focus Selection] = "focusselection_2")),
Group = Table.Group(#"Filtered Rows", {"common_data_id", "Focus Selection"}, {{"All Rows", each _, type table [common_data_id=nullable number, Focus Selection=nullable text, Attribute.2=nullable text, Value=any]}}),
#"Pivot All Rows" = Table.TransformColumns( Group, {{"All Rows", each 
Table.Pivot(_, List.Distinct(_[Attribute.2]), "Attribute.2", "Value", List.First)
}}),
#"Expanded All Rows" = Table.ExpandTableColumn(#"Pivot All Rows", "All Rows", {"_details", "_occurances", "_participating"}, {"_details", "_occurances", "_participating"}),
#"Changed Types 2" = Table.TransformColumnTypes(#"Expanded All Rows",{{"_details", type text}, {"_occurances", Int64.Type}, {"_participating", Int64.Type}})
in
#"Changed Types 2"

 Hope this helps

[ct_tm]

Thank you @collinsg this gots close enough. I had to modify the first filter to allow 1 through 5 instead of just 1 and 2 which created a bunch of uneccessary rows. So, I added a conditional to check if all the values were null then filtered them out.

Thanks again for your help, here was my final code if you are interested.

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"common_data_id", Int64.Type}, {"focusselection_1", type text}, {"focusselection_1_details", type text}, {"focusselection_1_occurances", Int64.Type}, {"focusselection_1_participating", Int64.Type}, {"focusselection_2", type text}, {"focusselection_2_details", type text}, {"focusselection_2_occurances", Int64.Type}, {"focusselection_2_participating", Int64.Type}, {"focusselection_3", type text}, {"focusselection_3_details", type text}, {"focusselection_3_occurances", Int64.Type}, {"focusselection_3_participating", Int64.Type}, {"focusselection_4", type text}, {"focusselection_4_details", type text}, {"focusselection_4_occurances", Int64.Type}, {"focusselection_4_participating", Int64.Type}, {"focusselection_5", type text}, {"focusselection_5_details", type text}, {"focusselection_5_occurances", Int64.Type}, {"focusselection_5_participating", Int64.Type}}),
Unpivoted = Table.UnpivotOtherColumns(#"Changed Type", {"common_data_id"}, "Attribute", "Value"),
#"Split attribute names" = Table.SplitColumn(Unpivoted, "Attribute", Splitter.SplitTextByPositions({0, 16}, false), {"Focus Selection", "Attribute.2"}),
#"Filtered Rows" = Table.SelectRows(#"Split attribute names", each [Focus Selection] = "focusselection_1" or [Focus Selection] = "focusselection_2" or [Focus Selection] = "focusselection_3" or [Focus Selection] = "focusselection_4" or [Focus Selection] = "focusselection_5"),
Group = Table.Group(#"Filtered Rows", {"common_data_id", "Focus Selection"}, {{"All Rows", each _, type table [common_data_id=nullable number, Focus Selection=nullable text, Attribute.2=nullable text, Value=any]}}),
#"Pivot All Rows" = Table.TransformColumns( Group, {{"All Rows", each 
Table.Pivot(_, List.Distinct(_[Attribute.2]), "Attribute.2", "Value", List.First)
}}),
#"Expanded All Rows" = Table.ExpandTableColumn(#"Pivot All Rows", "All Rows", {"", "_details", "_occurances", "_participating"}, {"Column1", "_details", "_occurances", "_participating"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded All Rows",{{"Column1", "_selection"}}),
    #"Changed Types 2" = Table.TransformColumnTypes(#"Renamed Columns",{{"_details", type text}, {"_occurances", Int64.Type}, {"_participating", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Types 2", "Custom", each if [_occurances] = null and [_participating] = null and [_details] = "null" and [_selection] = "null" then 1 else 0),
    #"Filtered Rows1" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Custom"})
in
    #"Removed Columns"