Creating a list from min and max values (Power Query)

[rblaze1001]

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

[rohit_singh · Accepted 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!

[rblaze1001 · Accepted solution]

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

[Jimyf]

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,

[v-yalanwu-msft · Accepted solution]

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.

[rblaze1001]

thanks a lot!

[wdx223_Daniel]

NewStep=#table({"Assignee","Receipt No."},List.TransformMany(Table.ToRows(PreviousStepName),each {_{1}.._{2}},(x,y)=>{x{0},y}))

[rohit_singh · Accepted 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!

[rblaze1001]

thanks a lot!

[rblaze1001 · Accepted solution]

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

[unhinged]

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.

[rblaze1001]

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.