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

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