Forum Discussion
Solved
Create a table based on num range in other table?
I'm a beginner in Power BI hoping to get som quick help. I have a table like this: In the table, some rows has a range in "NumberText". The range represents a range of rows in this table. I...
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, I havent tried last answer yet, but here is a more detailed example:
| Start: | ||||
| Name | Number | Endnumber | Range | |
| Main1 | 1000 | 1999 | 1000-1999 | |
| sub1 | 1020 | 1020 | ||
| sub2 | 1030 | 1030 | ||
| sub3 | 1040 | 1040 | ||
| Main2 | 2000 | 2999 | 2000-2999 | |
| sub4 | 2010 | 2010 | ||
| sub5 | 2020 | 2020 | ||
| sub6 | 2030 | 2030 | ||
| Main3 | 2000 | 2500 | 2000-2499 | |
| sub7 | 2410 | 2410 | ||
| sub8 | 2420 | 2420 | ||
| Result: | ||||
| NameMain | NumberMain | Name | Number | Range |
| Main1 | 1000 | Main1 | 1000 | 1000-1999 |
| Main1 | 1000 | sub1 | 1020 | |
| Main1 | 1000 | sub2 | 1030 | |
| Main1 | 1000 | sub3 | 1040 | |
| Main2 | 2000 | Main2 | 2000 | 2000-2999 |
| Main2 | 2000 | sub4 | 2010 | |
| Main2 | 2000 | sub5 | 2020 | |
| Main2 | 2000 | sub6 | 2030 | |
| Main2 | 2000 | sub7 | 2410 | |
| Main2 | 2000 | sub8 | 2420 | |
| Main3 | 2000 | Main3 | 2000 | 2000-2499 |
| Main3 | 2000 | sub7 | 2410 | |
| Main3 | 2000 | sub8 | 2420 |
Note the duplicates sub7 and sub8
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 š