Create a table based on num range in other table?

[Aerobat]

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 want an output table with:

Name(the "range name"), Number(the "range name"), Name ("current row"), Number ("current row")  

Meaning one row for each occurance of a row within a range. 

Example: Row 6 "Udført arbejde" will get 7 rows in new table:

Af- og nedskrivninger af anlæg	1000	Udført arbejde	1110
Indtjeningsbidrag	1000	Udført arbejde	1110
RESULTAT FØR SKAT	1000	Udført arbejde	1110
Resultat før renter	1000	Udført arbejde	1110
DÆKNINGSBIDRAG	1110	Udført arbejde	1110
OMSÆTNING	1110	Udført arbejde	1110
OMSÆTNING REGNINGSARBEJDE	1110	Udført arbejde	1110

[Jimmy801]

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]

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

[Fowmy · Accepted solution]

@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

[AlB]

@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]

@Aerobat 

Add a Column

={[Number]..[End Number]}

Then Expand to new row:

Paste the below code in blank query and check the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVUwVNJRMjQwMABTpgZQni6YHasDVWQEEjaFKEKi4PLGIBFziIS5kQGUpwtmwxWZgLUZQSgLcyhPF8yGKzIFCVtCTEKiYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Number = _t, #"End Number" = _t, #"Number Text" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"End Number", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {[Number]..[End Number]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

________________________

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

[AlB]

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 

[Aerobat]

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