Forum Discussion
Creating a list from min and max values (Power Query)
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 |
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.
9 Replies
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 SelectColHow 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.
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
thanks a lot!
NewStep=#table({"Assignee","Receipt No."},List.TransformMany(Table.ToRows(PreviousStepName),each {_{1}.._{2}},(x,y)=>{x{0},y}))
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!
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,
