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.
Hi everyone,
I need an extra column in power query (list) from which I can generate a set of rows in power query for each of the items in the list.
The contents of the list must be based values in another column (shown below). The list should contain the range min1..max1 and min2..max2, where the ranges are split by an | sign. For instance the combination "1..5|10..15|20" would generate the list containing the values 1,2,3,4,5,10,11,12,13,14,15,20. Does anyone know how this is done in power query? Secondly, I would like to add as many rows as items in the list, where the new row value is the value from the list.
Looking forward to your responses!
Solved! Go to Solution.
Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtTTM60xNNDTMzStMQJSxgZKsTpAYQOQkBFIDMgwMoSIGoPoWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ListsAsText = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ListsAsText", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Combine(List.Transform(Text.Split([ListsAsText], "|"), each Expression.Evaluate("{"&_&"}")))),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
#"Expanded Custom"
Pat
Can you please provide sample data fileso that I can help you quicker.
=Table.AddColumn(PreviousStepName,"Custom",each Expression.Evaluate("{"&Text.Replace([Samentelling],"|",",")&"}"))
Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtTTM60xNNDTMzStMQJSxgZKsTpAYQOQkBFIDMgwMoSIGoPoWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ListsAsText = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ListsAsText", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Combine(List.Transform(Text.Split([ListsAsText], "|"), each Expression.Evaluate("{"&_&"}")))),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
#"Expanded Custom"
Pat
Thanks, this worked!
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.