Thank you SO much @Anonymous
Your solution got me most of the way there so I could tweak it and get the result I wanted with the following M query
let
Source = Excel.Workbook(File.Contents("D:\Documents\Go green customers (2).xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Go green", "Column12"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Delivery notes", "Order Dispatched"}, {"Orders", "Order Confirmation"}, {"Invoices", "Invoice Email"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Quotes", type logical}, {"Order Confirmation", type logical}, {"Invoice Email", type logical}, {"Order Dispatched", type logical}, {"statements", type logical}, {"Credit Notes", type logical}, {"Email", type text}, {"Contact name", type text}, {"Company", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Id", "Contact name", "Company", "Email"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Id"}, {{"Count", each _, type table [Id=text, Contact name=text, Company=text, Email=text, Attribute=text, Value=logical]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Rank",1,1)),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Count"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Contact name", "Company", "Email", "Attribute", "Value", "Rank"}, {"Custom.Contact name", "Custom.Company", "Custom.Email", "Custom.Attribute", "Custom.Value", "Custom.Rank"}),
#"Removed Columns2" = Table.RemoveColumns(#"Expanded Custom",{"Custom.Contact name", "Custom.Company"}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns2", {{"Custom.Rank", type text}}, "en-GB"),{"Custom.Attribute", "Custom.Rank"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
#"Filtered Rows" = Table.SelectRows(#"Merged Columns", each ([Custom.Value] = true)),
#"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Merged]), "Merged", "Custom.Email"),
#"Removed Columns3" = Table.RemoveColumns(#"Pivoted Column",{"Custom.Value"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns3",{{"Invoice Email-3", "Invoice Email 1"}, {"Invoice Email-9", "Invoice Email 2"}, {"Invoice Email-15", "Invoice Email 3"}, {"Invoice Email-21", "Invoice Email 4"}, {"Invoice Email-27", "Invoice Email 5"}, {"Invoice Email-39", "Invoice Email 6"}, {"Invoice Email-33", "Invoice Email 7"}, {"Invoice Email-45", "Invoice Email 8"}, {"Invoice Email-51", "Invoice Email 9"}, {"statements-5", "Statements Email 1"}, {"statements-11", "Statements Email 2"}, {"statements-17", "Statements Email 3"}, {"statements-23", "Statements Email 4"}, {"statements-29", "Statements Email 5"}, {"statements-41", "Statements Email 6"}, {"statements-35", "Statements Email 7"}, {"statements-53", "Statements Email 8"}, {"Credit Notes-6", "Credit Notes Email 1"}, {"Credit Notes-12", "Credit Notes Email 2"}, {"Credit Notes-18", "Credit Notes Email 3"}, {"Credit Notes-24", "Credit Notes Email 4"}, {"Credit Notes-30", "Credit Notes Email 5"}, {"Credit Notes-42", "Credit Notes Email 6"}, {"Credit Notes-48", "Credit Notes Email 7"}, {"Credit Notes-54", "Credit Notes Email 8"}, {"Credit Notes-36", "Credit Notes Email 9"}, {"Quotes-1", "Quotes Emails 1"}, {"Quotes-7", "Quotes Emails 2"}, {"Quotes-19", "Quotes Emails 3"}, {"Quotes-13", "Quotes Emails 4"}, {"Quotes-25", "Quotes Emails 5"}, {"Quotes-37", "Quotes Emails 6"}, {"Quotes-55", "Quotes Emails 7"}, {"Quotes-31", "Quotes Emails 8"}, {"Quotes-43", "Quotes Emails 9"}, {"Order Confirmation-2", "Order Confirmation Email 1"}, {"Order Confirmation-8", "Order Confirmation Email 2"}, {"Order Confirmation-20", "Order Confirmation Email 3"}, {"Order Confirmation-14", "Order Confirmation Email 4"}, {"Order Confirmation-26", "Order Confirmation Email 5"}, {"Order Confirmation-32", "Order Confirmation Email 6"}, {"Order Confirmation-38", "Order Confirmation Email 7"}, {"Order Confirmation-56", "Order Confirmation Email 8"}, {"Order Confirmation-44", "Order Confirmation Email 9"}, {"Order Dispatched-4", "Order Dispatched Email 1"}, {"Order Dispatched-10", "Order Dispatched Email 2"}, {"Order Dispatched-28", "Order Dispatched Email 3"}, {"Order Dispatched-16", "Order Dispatched Email 4"}, {"Order Dispatched-22", "Order Dispatched Email 5"}, {"Order Dispatched-40", "Order Dispatched Email 6"}, {"Order Dispatched-34", "Order Dispatched Email 7"}})
in
#"Renamed Columns1"
LEGEND!!! 😁
