Get common part from Initial and Final range

[cgkas]

Hello I have a table like below that shows initial and final ranges. I'd like to get the common part of the ranges and then merge N1+Common Range, so the output would be the 4th column with colors in blue.

Is there are no Initial Range and Final Range, then result value would be only N1

If there are no common part between Initial and Final Ranges, then result would be N1 too.

How can be do this is M language to apply in Excel Power Query?

N1	Initial Range	Final Range	N1 + Common Range
70	2000000	2999999	70-2
78			78
79	0000000	9999999	79
67	2090000	2099999	67-209
66	9100000	9100999	66-9100
68	0000000	9999999	68
69	0000000	9999999	69
661	000000	000999	661-000
661	000010	000090	661-0000
661	002000	002999	661-002
661	009000	009999	661-009
50			50
550			550
3	00000	99999	3
91	0100000	0109999	91-010
264	235	235	264-235

[OwenAuger · Accepted solution]

@cgkas 

Here's some M code illustrating one way to do this.

The main steps: I used Text.ToList to convert Initial Range & Final Range to lists, combined them with List.Zip, then used List.FirstN to find the Common Range. Finally, the N1 + Common Range column is added, only including the Common Range if its length is more than zero.

Note: This should work whether empty Initial Range or Final Range are empty strings or nulls.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZBJDsAgCEXvwtoFYh04S+P9r1GkQrRNS4z58Jj0PKEiBCBUG4rVoAdBTQLjqMOi0PN4yStVW7C1wAWVkRu9SpSj9t3wZ1aJzm7xJnES2WgjZDX0rGEjPifj8va8eckWsNU0ytrJXyrKEZVjfEvK8+79Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [N1 = _t, #"Initial Range" = _t, #"Final Range" = _t]),
    #"Add Zipped Character List" = Table.AddColumn(Source, "Common Range", each List.Zip({try Text.ToList([Initial Range]) otherwise {},try Text.ToList([Final Range]) otherwise {}}), type list),
    #"Turn List into Common Range List" = Table.TransformColumns(#"Add Zipped Character List", { {"Common Range", each List.Transform(List.FirstN(_, each _{0}=_{1}), each _{0}) } }),
    #"Combine List into Text" = Table.TransformColumns( #"Turn List into Common Range List", { {"Common Range", Text.Combine, type text} } ),
    #"Add N1 + Common Range" = Table.AddColumn(#"Combine List into Text", "N1 + Common Range", each [N1] & (if Text.Length([Common Range])>0 then "-" & [Common Range] else ""), type text),
    #"Remove Common Range" = Table.RemoveColumns(#"Add N1 + Common Range",{"Common Range"})
in
    #"Remove Common Range"

Regards,

Owen

[cgkas]

Hello Owen,

Thanks so much. It works pretty fine.

One question:

I normally import from Table and my Source command looks like this:

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

How do you do to have a Source like yours?

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZBJDsAgCEXvwtoFYh04S+P9r1GkQrRNS4z58Jj0PKEiBCBUG4rVoAdBTQLjqMOi0PN4yStVW7C1wAWVkRu9SpSj9t3wZ1aJzm7xJnES2WgjZDX0rGEjPifj8va8eckWsNU0ytrJXyrKEZVjfEvK8+79Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [N1 = _t, #"Initial Range" = _t, #"Final Range" = _t]),

Best regards

[OwenAuger]

@cgkas 

Glad it works 🙂

The Table.FromRows(Json.Document(... is generated automatically by Power Query (within Power BI Desktop) when you choose "Enter Data" in the interface. I just used "Enter Data" as a convenient way of pasting in your sample table so that the code I posted in the forum was self-contained and didn't rely on a reference to an Excel table. You can of course replace this step with  code referring to the actual source of your data such as Excel.CurrentWorkbook(...).

Note: Since Excel Power Query doesn't have an Enter Data option in the interface, I created the query in Power BI Desktop then pasted the code into Excel Power Query. Once the code is pasted into Excel, you are able to edit the Enter Data step if needed.

Regards,

Owen

[cgkas]

Thanks for answer Owen and explanation.

Best regards