Generate columns by permutating numbers in first column

Hi Sab ,

We can use the merge function in Power Query Editor to meet your requirement.

1. Split the Value to four columns.

2. Then merge them to one column.

3. Then split into Rows, we can get four rows.

4. And merge as new table based on Date column, Join Kind is Full Outer, merge three times.

5. At last create add six conditional columns to filter duplicate rows. Then delete them.

The two tables M query as following,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA31DdT0lEyNDI2UYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, value = _t]),
    #"Inserted First Characters" = Table.AddColumn(Source, "First Characters", each Text.Start([value], 1), type text),
    #"Inserted Text Range" = Table.AddColumn(#"Inserted First Characters", "Text Range", each Text.Middle([value], 1, 1), type text),
    #"Inserted Text Range1" = Table.AddColumn(#"Inserted Text Range", "Text Range.1", each Text.Middle([value], 2, 1), type text),
    #"Inserted Last Characters" = Table.AddColumn(#"Inserted Text Range1", "Last Characters", each Text.End([value], 1), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Last Characters",{"value"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"First Characters", "Text Range", "Text Range.1", "Last Characters"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns", {{"Merged", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged"),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Merged", "Merged"}})
in
    #"Renamed Columns"

let
    Source = Table.NestedJoin(Table, {"Date"}, Table, {"Date"}, "Table", JoinKind.FullOuter),
    #"Expanded Table" = Table.ExpandTableColumn(Source, "Table", {"Merged"}, {"Table.Merged"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Table", {"Date"}, Table, {"Date"}, "Table", JoinKind.FullOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"Merged"}, {"Table.Merged.1"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Table1", {"Date"}, Table, {"Date"}, "Table", JoinKind.FullOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries1", "Table", {"Merged"}, {"Table.Merged.2"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Table2", "Custom", each if [Merged] = [Table.Merged] then 0 else 1),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [Merged] = [Table.Merged.1] then 0 else 1),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Custom.2", each if [Merged] = [Table.Merged.2] then 0 else 1),
    #"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "Custom.3", each if [Table.Merged] = [Table.Merged.1] then 0 else 1),
    #"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "Custom.4", each if [Table.Merged] = [Table.Merged.2] then 0 else 1),
    #"Added Conditional Column5" = Table.AddColumn(#"Added Conditional Column4", "Custom.5", each if [Table.Merged.1] = [Table.Merged.2] then 0 else 1),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column5", each ([Custom] = 1) and ([Custom.1] = 1) and ([Custom.2] = 1) and ([Custom.3] = 1) and ([Custom.4] = 1) and ([Custom.5] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns", {{"Merged", type text}, {"Table.Merged", type text}, {"Table.Merged.1", type text}, {"Table.Merged.2", type text}}, "en-US"),{"Merged", "Table.Merged", "Table.Merged.1", "Table.Merged.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged.1"),
    #"Changed Type" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged.1", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Merged.1", Order.Ascending}})
in
    #"Sorted Rows"

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.