Forum Discussion

rblaze1001's avatar
rblaze1001
New Member
3 years ago
Solved

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 ...
Need Help
Show and Tell
  • rohit_singh's avatar
    rohit_singh
    3 years ago

    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's avatar
    rblaze1001
    3 years ago

    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

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    3 years ago

    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.

unhinged's avatar
unhinged
Frequent Visitor
3 years ago
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's avatar
    rblaze1001
    New Member
    3 years ago

    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.