Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.