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 🙂
Hello Aerobat
check out if this solution could suit for you
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTI0MDAAUiYmQCpWJ1opCUnMFCqWDBIzNDSAUSCxFBDHFKzOEKYuFcQxgoiZgcRiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Number = _t, EndNumber = _t]),
ChangeType = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Number", Int64.Type}, {"EndNumber", Int64.Type}}),
DefineType = Table.AddColumn(ChangeType, "IsNoRange", each [Number]=[EndNumber]),
GetNoRange = Table.SelectRows(DefineType, each ([IsNoRange] = true)),
AssignRangeObjects = Table.AddColumn
(
GetNoRange,
"Assigned range objects",
(add)=> Table.SelectRows
(
DefineType,
each [IsNoRange]=false and [Number]<= add[Number] and [EndNumber]>= add[Number]
)
)
in
AssignRangeObjects
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
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
Paste this in a blank query to see 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]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Number", Int64.Type}, {"Endnumber", Int64.Type}, {"Range", type text}}), auxT = Table.SelectRows(#"Changed Type", each [Range]<> ""), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Range]<>"" then {[Name]} else Table.SelectRows(auxT, (inner)=>inner[Number]<=[Number] and inner[Endnumber]>=[Number])[Name]), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom", "Name Main"}}), #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Number Main", each Table.SelectRows(auxT, (inner)=>inner[Name]=[Name Main])[Number]{0}), #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Name Main", "Number Main", "Name", "Number", "Endnumber", "Range"}), #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Endnumber"}) in #"Removed Columns"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
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 🙂
