Forum Discussion

Aerobat's avatar
Aerobat
Frequent Visitor
5 years ago
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...
Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    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 🙂

    YouTube  LinkedIn

Jimmy801's avatar
Jimmy801
Community Champion
5 years ago

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

Aerobat's avatar
Aerobat
Frequent Visitor
5 years ago

Hi, I havent tried last answer yet, but here is a more detailed example:

Start:    
NameNumberEndnumberRange 
Main1100019991000-1999 
sub110201020  
sub210301030  
sub310401040  
Main2200029992000-2999 
sub420102010  
sub520202020  
sub620302030  
Main3200025002000-2499 
sub724102410  
sub824202420  
     
     
Result:    
     
NameMainNumberMainNameNumberRange
Main11000Main110001000-1999
Main11000sub11020 
Main11000sub21030 
Main11000sub31040 
Main22000Main220002000-2999
Main22000sub42010 
Main22000sub52020 
Main22000sub62030 
Main22000sub72410 
Main22000sub82420 
Main32000Main320002000-2499
Main32000sub72410 
Main32000sub82420 

 

Note the duplicates sub7 and sub8

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Aerobat 

    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 

     

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    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 🙂

    YouTube  LinkedIn