Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
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
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
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
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
Thanks for answer Owen and explanation.
Best regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
97 | |
75 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |