Table.Partition

[RB160458]

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

https://blog.crossjoin.co.uk/2014/10/07/creating-histograms-with-power-query/

and I'm trying to use the function to split a large table into subsets.

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-sample-csv-files-data-sets-for-testing-sales/

 

[RB160458]

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