Forum Discussion
Create a table based on num range in other table?
Aerobat
Paste the code in a blank Query and check the steps:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY0xDoMwEAT/4hqk9flI4kfkBRYFdDQ0Ef9Ht4dzUI09a2taS99l23MaUgZgqLVet5HneWjpd6z+RBDoi1AVBPpSqBQBLla0T+JF8aLdRrkVlTIj0JeJShDoy4uqIPAvlltxcrCoUXybVC/qo/ih8qJexfkE", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Number = _t, Endnumber = _t, Range = _t]), Table1 = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Endnumber", Int64.Type}}), #"Filtered Rows1" = Table.SelectRows(Table1, each ([Range] <> "")), #"Added Custom" = Table.AddColumn(#"Filtered Rows1", "Custom", (T1)=> Table.SelectRows(Table1, each [Number] >= T1[Number] and [Number] <= T1[Endnumber] )), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Number", "Endnumber", "Range"}, {"Name.1", "Number.1", "Endnumber.1", "Range.1"}), #"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"Name", "Number", "Name.1", "Number.1", "Range.1"}) in #"Removed Other Columns"________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
Hi Aerobat
This shouldn't be too difficult but I haven't managed to completely understand the requirement. Can you explain a bit more the logic behind those 7 lines fro Row 6? Perhaps provide an additional example?
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Ok, sorry if I was a bit unclear.
The rows with a range in "NumberText" is kind of headlines.
What I want is for each row with a range in NumberText, to create new rows for each row (without range) that has a "Number" within the range.
Hope this explains
