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.
Hi! Any ideas on how to do this in PowerQuery? Thanks in advance.
The available table looks like this:
Assignee | Receipt No. Min | Receipt No. Max |
A | 1001 | 1100 |
A | 2500 | 3000 |
B | 1101 | 1800 |
C | 3001 | 3200 |
My objective is to create a table wherein it will list per assignee all receipt nos. assigned to him like this:
Assignee | Receipt No. |
A | 1001 |
A | 1002 |
A | ..and so on until 1100.. then do the same from 2500 to 3000 |
B | 1101 |
B | 1102.. and so on until 1800 |
C | 3001 |
C | 3002.. and so on until 3200 |
Solved! Go to Solution.
Hi @rblaze1001 ,
Please try this. Copy and paste the below code in a blank query :
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MDAEUUBaKVYHImZkCuToKBkbQMWcIArA6iygYs4QBSAxYyOQWCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Assignee = _t, #"Receipt No. Min" = _t, #"Receipt No. Max" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Assignee", type text}, {"Receipt No. Min", Int64.Type}, {"Receipt No. Max", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {[Receipt No. Min]..[Receipt No. Max]}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Receipt No. Min", "Receipt No. Max"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Receipt No."}})
in
#"Renamed Columns"
Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!
hi @rohit_singh can you send the .pbix or .xlsx file you used for this? I tried to copy your solution in a blank query but it's not working. Thanks
Hi, @rblaze1001 ;
The code rohit_singh provided can be copied here
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test.
The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @rblaze1001
You can do it yourself in 3 easy steps:
Step 1: Add Column -> Custom Column
Step 2: Specify Min and Max columns
Step 3: Expand the New column to New Rows
You should get something like this:
You are then free to Remove the Min and Max columns if needed.
Best regards,
Hi, @rblaze1001 ;
The code rohit_singh provided can be copied here
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test.
The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thanks a lot!
NewStep=#table({"Assignee","Receipt No."},List.TransformMany(Table.ToRows(PreviousStepName),each {_{1}.._{2}},(x,y)=>{x{0},y}))
Hi @rblaze1001 ,
Please try this. Copy and paste the below code in a blank query :
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MDAEUUBaKVYHImZkCuToKBkbQMWcIArA6iygYs4QBSAxYyOQWCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Assignee = _t, #"Receipt No. Min" = _t, #"Receipt No. Max" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Assignee", type text}, {"Receipt No. Min", Int64.Type}, {"Receipt No. Max", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {[Receipt No. Min]..[Receipt No. Max]}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Receipt No. Min", "Receipt No. Max"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Receipt No."}})
in
#"Renamed Columns"
Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!
thanks a lot!
hi @rohit_singh can you send the .pbix or .xlsx file you used for this? I tried to copy your solution in a blank query but it's not working. Thanks
let
Source = Excel.CurrentWorkbook(){0}[Content],
AllNumbersInRange = Table.AddColumn(Source, "Receipt No.", (x)=> List.Numbers(x[Receipt No. Min], x[Receipt No. Max] - x[Receipt No. Min] + 1, 1 ) ),
Expand = Table.ExpandListColumn(AllNumbersInRange, "Receipt No."),
SelectCol = Table.SelectColumns(Expand,{"Assignee", "Receipt No."})
in
SelectCol
How do you pin an Excel file in here? Can't seem to find a button for that.
hi @unhinged , I'm also not familiar how to pin a file here in the reply. But I've seen some who were able to do it. Would appreciate if you can share the file you used. Thanks.
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.