Dynamically insert new columns in Excel Power Query and sort column pairs horizontally

[nik_l]

I'm stumped with a requirement and can't seem to get past it (I've tried to scour the internet, and I'm unable to fix it myself, given my lesser experience with Power Query). I could accomplish this using VBA, but the number of records, that need to be processed are shy of half a million records!

So here's the simplified task at hand. There are two columns - "Substring" and "Main String", as shown below [Input]:

Substring	Main String
AB|CDE	ABCDEF
ABC|DE|GH	ABCDEFGHI
A|BC|X|YZ	ABCDYZ

<tbody>
</tbody>

The Substring column has mini substrings separated by a delimiter (|). These Mini Substrings may or may not be found within the Main string. The output at the end should look like this [Expected Output]:

Substring	Main String	MSS1	ML1	MSS2	ML2	MSS3	ML3	MSS4	ML4
AB|CDE	ABCDEF	CDE	3	AB
ABC|DE|GH	ABCDEFGHI	ABC	3	DE	2	GH	2
A|BC|X|YZ	ABCDYZ	BC	2	YZ	2	A	1	X	0

<tbody>
</tbody>

(Note: MSS and ML in the column headers stand for Mini Substring and Match Length respectively)

The Mini Substrings can vary in number, so the row that has the maximum number of Mini Substrings would define the number of MSS and ML columns. In this case, the third row has four of them, so there were four MSS and ML columns each.

Not just that, the sequence of these Mini Substrings has to be such that those with highest matching lengths should be placed on the leftmost MSS and ML pair of columns. And then those with the least matching lengths should go on the rightmost pair of MSS and ML columns. And everything in between should progress in that order.

I got until the stage where the current output looks like this [Interim Output]:

Substring	Main String	MSS1	MSS2	MSS3	MSS4
AB|CDE	ABCDEF	AB	CDE
ABC|DE|GH	ABCDEFGHI	ABC	DE	GH
A|BC|X|YZ	ABCDYZ	A	BC	X	YZ

<tbody>
</tbody>

Dynamically inserting the ML columns just after the respective MSS columns is the challenge I'm unable to get past by. And I'm sure sorting them horizontally by the rank of matching lengths (i.e. lengths of those mini substrings that found a match in the main string) would also be another challenge. Here's the Power Query that I could come up with that generated the [Interim Output].

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sub String", type text}, {"Main String", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Sub String", "For Split"),
    #"Added Custom" = Table.AddColumn(#"Duplicated Column", "Split Count", each List.Count(Text.Split([Sub String],"|"))),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Split Count", "Sub String", "Main String", "For Split"}),
    #"Max No Of Splittable Columns" = List.Max(#"Reordered Columns"[Split Count]),
    #"List Of MSS Columns" = List.Transform({1..#"Max No Of Splittable Columns"}, each "MSS"&Text.From(_)),
    #"Split Columns By Delimiter" = Table.SplitColumn(#"Reordered Columns","For Split",Splitter.SplitTextByDelimiter("|"), #"List Of MSS Columns"),
    #"Removed Columns" = Table.RemoveColumns(#"Split Columns By Delimiter",{"Split Count"})
in
    #"Removed Columns"

Could you please guide me out of this? You can easily tell I am new to Power Query, so I very much appreciate any help that I can get. Thank you!

Note: I've also posted this on a different forum, but awaiting a response there.

 

[alansidman]

Here is the Mcode for adding columns and inserting the length of each column and then arranging their location. I am unable to determine how to sort horizontally as you desire

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Substring", type text}, {"Main String", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Substring", "Substring - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Substring - Copy", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Substring - Copy.1", "Substring - Copy.2", "Substring - Copy.3", "Substring - Copy.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Substring - Copy.1", type text}, {"Substring - Copy.2", type text}, {"Substring - Copy.3", type text}, {"Substring - Copy.4", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Text.Length([#"Substring - Copy.1"])),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Substring", "Main String", "Substring - Copy.1", "Custom", "Substring - Copy.2", "Substring - Copy.3", "Substring - Copy.4"}),
    #"Added Custom1" = Table.AddColumn(#"Reordered Columns", "Custom.1", each Text.Length([#"Substring - Copy.2"])),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom1",{"Substring", "Main String", "Substring - Copy.1", "Custom", "Substring - Copy.2", "Custom.1", "Substring - Copy.3", "Substring - Copy.4"}),
    #"Added Custom2" = Table.AddColumn(#"Reordered Columns1", "Custom.2", each Text.Length([#"Substring - Copy.3"])),
    #"Reordered Columns2" = Table.ReorderColumns(#"Added Custom2",{"Substring", "Main String", "Substring - Copy.1", "Custom", "Substring - Copy.2", "Custom.1", "Substring - Copy.3", "Custom.2", "Substring - Copy.4"}),
    #"Added Custom3" = Table.AddColumn(#"Reordered Columns2", "Custom.3", each Text.Length([#"Substring - Copy.4"]))
in
    #"Added Custom3"

To determine the length of a column use
Insert Custom column
Type = Text.Length([Column Name])

 

[nik_l]

Hi Alan,


Thanks for your reply!


This would have been good enough had the Substring column had a maximum of four predefined mini substrings. Unfortunately, that isn't the case. They can be 10, 15, or even 20. In essence, the Substring column can be split into a varying number of columns depending on the incoming data.


For instance, one of the biggest substrings can look like this: AA|AB|AC|AD|AE|AF|AG|AH|AI|AJ


Another instance can be that the biggest substring is just this: AA|AB


Sorry if I couldn't make this clear in my original post.


How do I handle such dynamism?

Note: I've just posted a reply and I don't see it reflecting here, so I'm repeating my reply