Results 1 to 2 of 2

Thread: Table.Partition

  1. #1
    Seeker RB160458's Avatar
    Join Date
    Dec 2019
    Posts
    8
    Articles
    0
    Excel Version
    Excel 365

    Table.Partition



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I've noticed an interesting article by Chris Webb on Table.Partition

    https://blog.crossjoin.co.uk/2014/10...h-power-query/

    and I'm trying to use the function to split a large table into subsets.
    Code:
    let
        Source = Csv.Document(File.Contents("C:\Users\richa\Excel\PowerQuery\PowerQuery Data\500000 Sales Records.csv"),[Delimiter=",", Columns=14, Encoding=1252, QuoteStyle=QuoteStyle.None]),
        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
        BucketHashFunction = (Region) as text => Region,
        Partitioned = Table.Partition(#"Promoted Headers","Region", 7, BucketHashFunction),
        Partitioned1 = Partitioned{0}
        
    in
        Partitioned1
    I seem to be able to split the table into a list of tables owever when I try and access one of the sub tables I am getting an error

    Expression.Error: We cannot convert the value "Sub-Saharan Africa" to type Number.
    Details:
    Value=Sub-Saharan Africa
    Type=[Type]

    The sample data that I am using comes from http://eforexcel.com/wp/downloads-18...testing-sales/
    Last edited by AliGW; 2019-12-29 at 09:25 AM. Reason: Code added to post for the benefit of all. Please do this yourself in future. Thanks.

  2. #2
    Seeker RB160458's Avatar
    Join Date
    Dec 2019
    Posts
    8
    Articles
    0
    Excel Version
    Excel 365
    ok, i sorted it by adding an index to table of unique Regions and merging it with the main table then:

    let
    Source = Table.NestedJoin(#"50000 Sales Records (3)", {"Region"}, Regions, {"Region"}, "Regions", JoinKind.LeftOuter),
    #"Expanded Regions" = Table.ExpandTableColumn(Source, "Regions", {"Index"}, {"Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Regions",{"Index", "Region", "Country", "Item Type", "Sales Channel", "Order Priority", "Order Date", "Order ID", "Ship Date", "Units Sold", "Unit Price", "Unit Cost", "Total Revenue", "Total Cost", "Total Profit"}),
    #"Partitioned" = Table.Partition(#"Reordered Columns", "Index" ,7 , each _),
    Partitioned1 = Partitioned{1}
    in
    Partitioned1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •