Hi All,
Im trying to make the following codes more "efficient" since it seems something could be removed or modify for faster calculations:
1. text concatenation
2. count the duplicates (unpivot, count rows, sum count of rows)
let
Source = Csv.Document(File.Contents("D:\xxx\Documents\1m\AUTOMATIC\files\sapmple.csv"),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Removed Blank" = Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Added ID" = Table.AddColumn(#"Removed Blank", "ID", each [Column1]&"-"&[Column2]&"-"&[Column3]&"-"&[Column4]&"-"&[Column5]&"-"&[Column6]),
#"Added P1" = Table.AddColumn(#"Added ID", "P1", each [Column1]&"-"&[Column2]&"-"&[Column3]&"-"&[Column4]&"-"&[Column5]),
#"Added P2" = Table.AddColumn(#"Added P1", "P2", each [Column1]&"-"&[Column2]&"-"&[Column3]&"-"&[Column4]&"-"&[Column6]),
#"Added P3" = Table.AddColumn(#"Added P2", "P3", each [Column1]&"-"&[Column2]&"-"&[Column3]&"-"&[Column5]&"-"&[Column6]),
#"Added P4" = Table.AddColumn(#"Added P3", "P4", each [Column1]&"-"&[Column2]&"-"&[Column4]&"-"&[Column5]&"-"&[Column6]),
#"Added P5" = Table.AddColumn(#"Added P4", "P5", each [Column1]&"-"&[Column3]&"-"&[Column4]&"-"&[Column5]&"-"&[Column6]),
#"Added P6" = Table.AddColumn(#"Added P5", "P6", each [Column2]&"-"&[Column3]&"-"&[Column4]&"-"&[Column5]&"-"&[Column6]),
#"Added Q1" = Table.AddColumn(#"Added P6", "Q1", each [Column1]&"-"&[Column2]&"-"&[Column3]&"-"&[Column4]),
#"Added Q2" = Table.AddColumn(#"Added Q1", "Q2", each [Column1]&"-"&[Column2]&"-"&[Column3]&"-"&[Column5]),
#"Added Q3" = Table.AddColumn(#"Added Q2", "Q3", each [Column1]&"-"&[Column2]&"-"&[Column3]&"-"&[Column6]),
#"Added Q4" = Table.AddColumn(#"Added Q3", "Q4", each [Column1]&"-"&[Column2]&"-"&[Column4]&"-"&[Column5]),
#"Added Q5" = Table.AddColumn(#"Added Q4", "Q5", each [Column1]&"-"&[Column2]&"-"&[Column4]&"-"&[Column6]),
#"Added Q6" = Table.AddColumn(#"Added Q5", "Q6", each [Column1]&"-"&[Column2]&"-"&[Column5]&"-"&[Column6]),
#"Added Q7" = Table.AddColumn(#"Added Q6", "Q7", each [Column1]&"-"&[Column3]&"-"&[Column4]&"-"&[Column5]),
#"Added Q8" = Table.AddColumn(#"Added Q7", "Q8", each [Column1]&"-"&[Column3]&"-"&[Column4]&"-"&[Column6]),
#"Added Q9" = Table.AddColumn(#"Added Q8", "Q9", each [Column1]&"-"&[Column3]&"-"&[Column5]&"-"&[Column6]),
#"Added Q10" = Table.AddColumn(#"Added Q9", "Q10", each [Column1]&"-"&[Column4]&"-"&[Column5]&"-"&[Column6]),
#"Added Q11" = Table.AddColumn(#"Added Q10", "Q11", each [Column2]&"-"&[Column3]&"-"&[Column4]&"-"&[Column5]),
#"Added Q12" = Table.AddColumn(#"Added Q11", "Q12", each [Column2]&"-"&[Column3]&"-"&[Column4]&"-"&[Column6]),
#"Added Q13" = Table.AddColumn(#"Added Q12", "Q13", each [Column2]&"-"&[Column3]&"-"&[Column5]&"-"&[Column6]),
#"Added Q14" = Table.AddColumn(#"Added Q13", "Q14", each [Column2]&"-"&[Column4]&"-"&[Column5]&"-"&[Column6]),
#"Added Q15" = Table.AddColumn(#"Added Q14", "Q15", each [Column3]&"-"&[Column4]&"-"&[Column5]&"-"&[Column6]),
#"Added T1" = Table.AddColumn(#"Added Q15", "T1", each [Column1]&"-"&[Column2]&"-"&[Column3]),
#"Added T2" = Table.AddColumn(#"Added T1", "T2", each [Column1]&"-"&[Column2]&"-"&[Column4]),
#"Added T3" = Table.AddColumn(#"Added T2", "T3", each [Column1]&"-"&[Column2]&"-"&[Column5]),
#"Added T4" = Table.AddColumn(#"Added T3", "T4", each [Column1]&"-"&[Column2]&"-"&[Column6]),
#"Added T5" = Table.AddColumn(#"Added T4", "T5", each [Column1]&"-"&[Column3]&"-"&[Column4]),
#"Added T6" = Table.AddColumn(#"Added T5", "T6", each [Column1]&"-"&[Column3]&"-"&[Column5]),
#"Added T7" = Table.AddColumn(#"Added T6", "T7", each [Column1]&"-"&[Column3]&"-"&[Column6]),
#"Added T8" = Table.AddColumn(#"Added T7", "T8", each [Column1]&"-"&[Column4]&"-"&[Column5]),
#"Added T9" = Table.AddColumn(#"Added T8", "T9", each [Column1]&"-"&[Column4]&"-"&[Column6]),
#"Added T10" = Table.AddColumn(#"Added T9", "T10", each [Column1]&"-"&[Column5]&"-"&[Column6]),
#"Added T11" = Table.AddColumn(#"Added T10", "T11", each [Column2]&"-"&[Column3]&"-"&[Column4]),
#"Added T12" = Table.AddColumn(#"Added T11", "T12", each [Column2]&"-"&[Column3]&"-"&[Column5]),
#"Added T13" = Table.AddColumn(#"Added T12", "T13", each [Column2]&"-"&[Column3]&"-"&[Column6]),
#"Added T14" = Table.AddColumn(#"Added T13", "T14", each [Column2]&"-"&[Column4]&"-"&[Column5]),
#"Added T15" = Table.AddColumn(#"Added T14", "T15", each [Column2]&"-"&[Column4]&"-"&[Column6]),
#"Added T16" = Table.AddColumn(#"Added T15", "T16", each [Column2]&"-"&[Column5]&"-"&[Column6]),
#"Added T17" = Table.AddColumn(#"Added T16", "T17", each [Column3]&"-"&[Column4]&"-"&[Column5]),
#"Added T18" = Table.AddColumn(#"Added T17", "T18", each [Column3]&"-"&[Column4]&"-"&[Column6]),
#"Added T19" = Table.AddColumn(#"Added T18", "T19", each [Column3]&"-"&[Column5]&"-"&[Column6]),
#"Added T20" = Table.AddColumn(#"Added T19", "T20", each [Column4]&"-"&[Column5]&"-"&[Column6]),
#"Added PA1" = Table.AddColumn(#"Added T20", "PA1", each [Column1]&"-"&[Column2]),
#"Added PA2" = Table.AddColumn(#"Added PA1", "PA2", each [Column1]&"-"&[Column3]),
#"Added PA3" = Table.AddColumn(#"Added PA2", "PA3", each [Column1]&"-"&[Column4]),
#"Added PA4" = Table.AddColumn(#"Added PA3", "PA4", each [Column1]&"-"&[Column5]),
#"Added PA5" = Table.AddColumn(#"Added PA4", "PA5", each [Column1]&"-"&[Column6]),
#"Added PA6" = Table.AddColumn(#"Added PA5", "PA6", each [Column2]&"-"&[Column3]),
#"Added PA7" = Table.AddColumn(#"Added PA6", "PA7", each [Column2]&"-"&[Column4]),
#"Added PA8" = Table.AddColumn(#"Added PA7", "PA8", each [Column2]&"-"&[Column5]),
#"Added PA9" = Table.AddColumn(#"Added PA8", "PA9", each [Column2]&"-"&[Column6]),
#"Added PA10" = Table.AddColumn(#"Added PA9", "PA10", each [Column3]&"-"&[Column4]),
#"Added PA11" = Table.AddColumn(#"Added PA10", "PA11", each [Column3]&"-"&[Column5]),
#"Added PA12" = Table.AddColumn(#"Added PA11", "PA12", each [Column3]&"-"&[Column6]),
#"Added PA13" = Table.AddColumn(#"Added PA12", "PA13", each [Column4]&"-"&[Column5]),
#"Added PA14" = Table.AddColumn(#"Added PA13", "PA14", each [Column4]&"-"&[Column6]),
#"Added PA15" = Table.AddColumn(#"Added PA14", "PA15", each [Column5]&"-"&[Column6]),
#"Unpivoted Pentad" = Table.UnpivotOtherColumns(#"Added PA15", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "ID", "Q1", "Q2", "Q3", "Q4", "Q5", "Q6", "Q7", "Q8", "Q9", "Q10", "Q11", "Q12", "Q13", "Q14", "Q15", "T1", "T2", "T3", "T4", "T5", "T6", "T7", "T8", "T9", "T10", "T11", "T12", "T13", "T14", "T15", "T16", "T17", "T18", "T19", "T20", "PA1", "PA2", "PA3", "PA4", "PA5", "PA6", "PA7", "PA8", "PA9", "PA10", "PA11", "PA12", "PA13", "PA14", "PA15"}, "Attribute", "Value"),
#"Removed Columns Pentad" = Table.RemoveColumns(#"Unpivoted Pentad",{"Attribute"}),
#"Grouped Rows Pentad" = Table.Group(#"Removed Columns Pentad", {"Value"}, {{"Count", each Table.RowCount(_), type number}, {"C1", each _, type table [Column1=text, Column2=text, Column3=text, Column4=text, Column5=text, Column6=text, ID=text, Q1=text, Q2=text, Q3=text, Q4=text, Q5=text, Q6=text, Q7=text, Q8=text, Q9=text, Q10=text, Q11=text, Q12=text, Q13=text, Q14=text, Q15=text, T1=text, T2=text, T3=text, T4=text, T5=text, T6=text, T7=text, T8=text, T9=text, T10=text, T11=text, T12=text, T13=text, T14=text, T15=text, T16=text, T17=text, T18=text, T19=text, T20=text, PA1=text, PA2=text, PA3=text, PA4=text, PA5=text, PA6=text, PA7=text, PA8=text, PA9=text, PA10=text, PA11=text, PA12=text, PA13=text, PA14=text, PA15=text, Value=anynonnull]}}),
#"Expanded C1 Pentad" = Table.ExpandTableColumn(#"Grouped Rows Pentad", "C1", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "ID", "Q1", "Q2", "Q3", "Q4", "Q5", "Q6", "Q7", "Q8", "Q9", "Q10", "Q11", "Q12", "Q13", "Q14", "Q15", "T1", "T2", "T3", "T4", "T5", "T6", "T7", "T8", "T9", "T10", "T11", "T12", "T13", "T14", "T15", "T16", "T17", "T18", "T19", "T20", "PA1", "PA2", "PA3", "PA4", "PA5", "PA6", "PA7", "PA8", "PA9", "PA10", "PA11", "PA12", "PA13", "PA14", "PA15"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "ID", "Q1", "Q2", "Q3", "Q4", "Q5", "Q6", "Q7", "Q8", "Q9", "Q10", "Q11", "Q12", "Q13", "Q14", "Q15", "T1", "T2", "T3", "T4", "T5", "T6", "T7", "T8", "T9", "T10", "T11", "T12", "T13", "T14", "T15", "T16", "T17", "T18", "T19", "T20", "PA1", "PA2", "PA3", "PA4", "PA5", "PA6", "PA7", "PA8", "PA9", "PA10", "PA11", "PA12", "PA13", "PA14", "PA15"}),
#"Grouped Rows1 Pentad" = Table.Group(#"Expanded C1 Pentad", {"ID"}, {{"Pentad Count", each List.Sum([Count])-5, type number}, {"C2", each _, type table [Value=anynonnull, Count=number, Column1=text, Column2=text, Column3=text, Column4=text, Column5=text, Column6=text, ID=text, Q1=text, Q2=text, Q3=text, Q4=text, Q5=text, Q6=text, Q7=text, Q8=text, Q9=text, Q10=text, Q11=text, Q12=text, Q13=text, Q14=text, Q15=text, T1=text, T2=text, T3=text, T4=text, T5=text, T6=text, T7=text, T8=text, T9=text, T10=text, T11=text, T12=text, T13=text, T14=text, T15=text, T16=text, T17=text, T18=text, T19=text, T20=text, PA1=text, PA2=text, PA3=text, PA4=text, PA5=text, PA6=text, PA7=text, PA8=text, PA9=text, PA10=text, PA11=text, PA12=text, PA13=text, PA14=text, PA15=text]}}),
#"Expanded C2 Pentad" = Table.ExpandTableColumn(#"Grouped Rows1 Pentad", "C2", {"Value", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Q1", "Q2", "Q3", "Q4", "Q5", "Q6", "Q7", "Q8", "Q9", "Q10", "Q11", "Q12", "Q13", "Q14", "Q15", "T1", "T2", "T3", "T4", "T5", "T6", "T7", "T8", "T9", "T10", "T11", "T12", "T13", "T14", "T15", "T16", "T17", "T18", "T19", "T20", "PA1", "PA2", "PA3", "PA4", "PA5", "PA6", "PA7", "PA8", "PA9", "PA10", "PA11", "PA12", "PA13", "PA14", "PA15"}, {"Value", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Q1", "Q2", "Q3", "Q4", "Q5", "Q6", "Q7", "Q8", "Q9", "Q10", "Q11", "Q12", "Q13", "Q14", "Q15", "T1", "T2", "T3", "T4", "T5", "T6", "T7", "T8", "T9", "T10", "T11", "T12", "T13", "T14", "T15", "T16", "T17", "T18", "T19", "T20", "PA1", "PA2", "PA3", "PA4", "PA5", "PA6", "PA7", "PA8", "PA9", "PA10", "PA11", "PA12", "PA13", "PA14", "PA15"}),
#"Removed Duplicates Pentad" = Table.Distinct(#"Expanded C2 Pentad", {"ID"}),
#"Unpivoted Columns1 Quad" = Table.UnpivotOtherColumns(#"Removed Duplicates Pentad", {"ID", "Pentad Count", "Value", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "T1", "T2", "T3", "T4", "T5", "T6", "T7", "T8", "T9", "T10", "T11", "T12", "T13", "T14", "T15", "T16", "T17", "T18", "T19", "T20", "PA1", "PA2", "PA3", "PA4", "PA5", "PA6", "PA7", "PA8", "PA9", "PA10", "PA11", "PA12", "PA13", "PA14", "PA15"}, "Attribute", "Value.Q1"),
#"Removed Columns1 Quad" = Table.RemoveColumns(#"Unpivoted Columns1 Quad",{"Attribute"}),
#"Grouped Rows2 Quad" = Table.Group(#"Removed Columns1 Quad", {"Value.Q1"}, {{"QCount", each Table.RowCount(_), type number}, {"QCount ALL", each _, type table [ID=text, Pentad Count=number, Value=text, Column1=text, Column2=text, Column3=text, Column4=text, Column5=text, Column6=text, T1=text, T2=text, T3=text, T4=text, T5=text, T6=text, T7=text, T8=text, T9=text, T10=text, T11=text, T12=text, T13=text, T14=text, T15=text, T16=text, T17=text, T18=text, T19=text, T20=text, PA1=text, PA2=text, PA3=text, PA4=text, PA5=text, PA6=text, PA7=text, PA8=text, PA9=text, PA10=text, PA11=text, PA12=text, PA13=text, PA14=text, PA15=text, Value.Q1=text]}}),
#"Expanded QCount ALL Quad" = Table.ExpandTableColumn(#"Grouped Rows2 Quad", "QCount ALL", {"ID", "Pentad Count", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "T1", "T2", "T3", "T4", "T5", "T6", "T7", "T8", "T9", "T10", "T11", "T12", "T13", "T14", "T15", "T16", "T17", "T18", "T19", "T20", "PA1", "PA2", "PA3", "PA4", "PA5", "PA6", "PA7", "PA8", "PA9", "PA10", "PA11", "PA12", "PA13", "PA14", "PA15"}, {"ID", "Pentad Count", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "T1", "T2", "T3", "T4", "T5", "T6", "T7", "T8", "T9", "T10", "T11", "T12", "T13", "T14", "T15", "T16", "T17", "T18", "T19", "T20", "PA1", "PA2", "PA3", "PA4", "PA5", "PA6", "PA7", "PA8", "PA9", "PA10", "PA11", "PA12", "PA13", "PA14", "PA15"}),
#"Grouped Rows3 Quad" = Table.Group(#"Expanded QCount ALL Quad", {"ID"}, {{"Quad Count", each List.Sum([QCount]), type number}, {"QCount ALL1", each _, type table [Value.Q1=text, QCount=number, ID=text, Pentad Count=number, Column1=text, Column2=text, Column3=text, Column4=text, Column5=text, Column6=text, T1=text, T2=text, T3=text, T4=text, T5=text, T6=text, T7=text, T8=text, T9=text, T10=text, T11=text, T12=text, T13=text, T14=text, T15=text, T16=text, T17=text, T18=text, T19=text, T20=text, PA1=text, PA2=text, PA3=text, PA4=text, PA5=text, PA6=text, PA7=text, PA8=text, PA9=text, PA10=text, PA11=text, PA12=text, PA13=text, PA14=text, PA15=text]}}),
#"Expanded QCount ALL1 Quad" = Table.ExpandTableColumn(#"Grouped Rows3 Quad", "QCount ALL1", {"Pentad Count", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "T1", "T2", "T3", "T4", "T5", "T6", "T7", "T8", "T9", "T10", "T11", "T12", "T13", "T14", "T15", "T16", "T17", "T18", "T19", "T20", "PA1", "PA2", "PA3", "PA4", "PA5", "PA6", "PA7", "PA8", "PA9", "PA10", "PA11", "PA12", "PA13", "PA14", "PA15"}, {"Pentad Count", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "T1", "T2", "T3", "T4", "T5", "T6", "T7", "T8", "T9", "T10", "T11", "T12", "T13", "T14", "T15", "T16", "T17", "T18", "T19", "T20", "PA1", "PA2", "PA3", "PA4", "PA5", "PA6", "PA7", "PA8", "PA9", "PA10", "PA11", "PA12", "PA13", "PA14", "PA15"}),
#"Removed Duplicates" = Table.Distinct(#"Expanded QCount ALL1 Quad", {"ID"}),
#"Triplets Unpivoted Columns2" = Table.UnpivotOtherColumns(#"Removed Duplicates", {"ID", "Quad Count", "Pentad Count", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "PA1", "PA2", "PA3", "PA4", "PA5", "PA6", "PA7", "PA8", "PA9", "PA10", "PA11", "PA12", "PA13", "PA14", "PA15"}, "Attribute", "Value"),
#"Triplets Removed Columns2" = Table.RemoveColumns(#"Triplets Unpivoted Columns2",{"Attribute"}),
#"Triplets Grouped Rows4" = Table.Group(#"Triplets Removed Columns2", {"Value"}, {{"TCount", each Table.RowCount(_), type number}, {"TCount ALL", each _, type table [ID=text, Quad Count=number, Pentad Count=number, Column1=text, Column2=text, Column3=text, Column4=text, Column5=text, Column6=text, PA1=text, PA2=text, PA3=text, PA4=text, PA5=text, PA6=text, PA7=text, PA8=text, PA9=text, PA10=text, PA11=text, PA12=text, PA13=text, PA14=text, PA15=text, Value=text]}}),
#"Triplets Expanded TCount ALL" = Table.ExpandTableColumn(#"Triplets Grouped Rows4", "TCount ALL", {"ID", "Quad Count", "Pentad Count", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "PA1", "PA2", "PA3", "PA4", "PA5", "PA6", "PA7", "PA8", "PA9", "PA10", "PA11", "PA12", "PA13", "PA14", "PA15"}, {"ID", "Quad Count", "Pentad Count", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "PA1", "PA2", "PA3", "PA4", "PA5", "PA6", "PA7", "PA8", "PA9", "PA10", "PA11", "PA12", "PA13", "PA14", "PA15"}),
#"Triplets Grouped Rows5" = Table.Group(#"Triplets Expanded TCount ALL", {"ID"}, {{"Triplet Count", each List.Sum([TCount]), type number}, {"TCount All1", each _, type table [Value=text, TCount=number, ID=text, Quad Count=number, Pentad Count=number, Column1=text, Column2=text, Column3=text, Column4=text, Column5=text, Column6=text, PA1=text, PA2=text, PA3=text, PA4=text, PA5=text, PA6=text, PA7=text, PA8=text, PA9=text, PA10=text, PA11=text, PA12=text, PA13=text, PA14=text, PA15=text]}}),
#"Triplets Expanded TCount All1" = Table.ExpandTableColumn(#"Triplets Grouped Rows5", "TCount All1", {"Quad Count", "Pentad Count", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "PA1", "PA2", "PA3", "PA4", "PA5", "PA6", "PA7", "PA8", "PA9", "PA10", "PA11", "PA12", "PA13", "PA14", "PA15"}, {"Quad Count", "Pentad Count", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "PA1", "PA2", "PA3", "PA4", "PA5", "PA6", "PA7", "PA8", "PA9", "PA10", "PA11", "PA12", "PA13", "PA14", "PA15"}),
#"Triplets Removed Duplicates1" = Table.Distinct(#"Triplets Expanded TCount All1", {"Quad Count", "Pentad Count", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "PA1", "PA2", "PA3", "PA4", "PA5", "PA6", "PA7", "PA8", "PA9", "PA10", "PA11", "PA12", "PA13", "PA14", "PA15"}),
#"Pair Unpivoted Columns3" = Table.UnpivotOtherColumns(#"Triplets Removed Duplicates1", {"ID", "Triplet Count", "Quad Count", "Pentad Count", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, "Attribute", "Value"),
#"Pair Removed Columns3" = Table.RemoveColumns(#"Pair Unpivoted Columns3",{"Attribute"}),
#"Pair Grouped Rows6" = Table.Group(#"Pair Removed Columns3", {"Value"}, {{"PACount", each Table.RowCount(_), type number}, {"PACount All", each _, type table [ID=text, Triplet Count=number, Quad Count=number, Pentad Count=number, Column1=text, Column2=text, Column3=text, Column4=text, Column5=text, Column6=text, Value=text]}}),
#"Pair Expanded PACount All" = Table.ExpandTableColumn(#"Pair Grouped Rows6", "PACount All", {"ID", "Triplet Count", "Quad Count", "Pentad Count", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"ID", "Triplet Count", "Quad Count", "Pentad Count", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}),
#"Pair Grouped Rows7" = Table.Group(#"Pair Expanded PACount All", {"ID"}, {{"Pair Count", each List.Sum([PACount]), type number}, {"PACount All1", each _, type table [Value=text, PACount=number, ID=text, Triplet Count=number, Quad Count=number, Pentad Count=number, Column1=text, Column2=text, Column3=text, Column4=text, Column5=text, Column6=text]}}),
#"Pair Expanded PACount All1" = Table.ExpandTableColumn(#"Pair Grouped Rows7", "PACount All1", {"Triplet Count", "Quad Count", "Pentad Count", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Triplet Count", "Quad Count", "Pentad Count", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}),
#"Pair Removed Duplicates2" = Table.Distinct(#"Pair Expanded PACount All1", {"ID"}),
#"Single Unpivoted Columns4" = Table.UnpivotOtherColumns(#"Pair Removed Duplicates2", {"ID", "Pair Count", "Triplet Count", "Quad Count", "Pentad Count"}, "Attribute", "Value"),
#"Single Removed Columns4" = Table.RemoveColumns(#"Single Unpivoted Columns4",{"Attribute"}),
#"Single Grouped Rows8" = Table.Group(#"Single Removed Columns4", {"Value"}, {{"SCount", each Table.RowCount(_), type number}, {"SCount All", each _, type table [ID=text, Pair Count=number, Triplet Count=number, Quad Count=number, Pentad Count=number, Value=text]}}),
#"Single Expanded SCount All" = Table.ExpandTableColumn(#"Single Grouped Rows8", "SCount All", {"ID", "Pair Count", "Triplet Count", "Quad Count", "Pentad Count"}, {"ID", "Pair Count", "Triplet Count", "Quad Count", "Pentad Count"}),
#"Single Grouped Rows9" = Table.Group(#"Single Expanded SCount All", {"ID"}, {{"Single Count", each List.Sum([SCount]), type number}, {"SCount All1", each _, type table [Value=text, SCount=number, ID=text, Pair Count=number, Triplet Count=number, Quad Count=number, Pentad Count=number]}}),
#"Single Expanded SCount All1" = Table.ExpandTableColumn(#"Single Grouped Rows9", "SCount All1", {"Pair Count", "Triplet Count", "Quad Count", "Pentad Count"}, {"Pair Count", "Triplet Count", "Quad Count", "Pentad Count"}),
#"Single Removed Duplicates3" = Table.Distinct(#"Single Expanded SCount All1", {"ID"}),
#"Filtered Rows" = Table.SelectRows(#"Single Removed Duplicates3", each ([ID] = "2-3-19-21-23-31"))
in
#"Filtered Rows"
