Generate columns by permutating numbers in first column

[Sab]

Hello,

I have a column containing 4 digits numbers, for example row1: 1234

I need to generate all possible permutations of this numer in NEW COLUMNS, for example"

Col2: 1243

Col3: 1324

Col4: 1342

...............

so total of 24 columns.

Thanks a lot

[v-zhenbw-msft]

Hi @Sab ,

How about the result after you follow the suggestions mentioned in my original post?

Could you please provide more details or expected result about it If it doesn't meet your requirement?

If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly.

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.

[Greg_Deckler]

@Sab - Are you trying to do this in DAX or Power Query or does it matter?

Can I ask why? Because it seems like a nasty thing to do and just wondering if there are other ways to accomplish the broader objective.

[Sab]

Some guy wants to crack the lottery I guess 😄

He wants to know is there a correlation between dates and specific number like: 

Date: 2012-06-06 -> value: 1519 -> Perm: 1951 Date: 2020-08-17
Date: 2012-06-08 -> value: 3419 -> Perm: 1349 Date: 2020-08-04
Date: 2012-06-12 -> value: 8064 -> Perm: 6048 Date: 2020-08-17
Date: 2012-06-14 -> value: 9704 -> Perm: 9074 Date: 2020-08-09
Date: 2012-06-22 -> value: 9456 -> Perm: 6549 Date: 2020-08-16
Date: 2012-06-06 -> value: 1519 -> Perm: 1951 Date: 2020-08-17

So what I thought is to genereate all permutations in separate columns, then Unpivot to create 24 rows for each date, then create relationship with original table (which has date and value columns)

I know its tricky and messy, thanks for your time @Greg_Deckler 

[v-zhenbw-msft · Accepted solution]

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.

[Sab]

Thanks for your reply.

Power Query would be great