Matrix table for min-max

[samahiji]

Hi,

I have the min & max value for X & Y and I want to generate two tables (two queries):

	X	Y
Min	1000	9000
Max	5000	60000
Diff	4000	51000

First table is a matrix table that contains the minX + 50 in the rows and minY + 50 in column util the max value of (max minus min).

Rows: MinX+50 until diffX

column: Min Y+50 until diffY

	1000	1050	1100	….	4000
9000	9000	9000	9000	9000	9000
9050	9050	9050	9050	9050	9050
9100	9100	9100	9100	9100	9100
9150	9150	9150	9150	9150	9150
9200	9200	9200	9200	9200	9200
9250	9250	9250	9250	9250	9250
	…	…	…	…	…
51000	51000	51000	51000	51000	51000

The second table is to construct the X-Y table for all possible combinations of matrix table above:

X	Y
1000	9000
1050	9000
1100	9000
…	…
4000	9000
1000	9050
1050	9050
1100	9050
…	…
4000	9050
…	…
…	…
4000	51000

Is this better doable in Power Query or I need to have a Excel macro?

[wini_R · Accepted solution]

Hi @samahiji,

I'm not sure if I understood your requirements right but possibly this approach could be useful for you or you can adjust it to your needs. Please paste the code below into Advanced editor in Power Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3MU9JRMjQwMABSliAqVgcomlgB5JpCRM0MYMIumWlpQAETiLgpWFdsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#" " = _t, X = _t, Y = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"X", Int64.Type}, {"Y", Int64.Type}}),
    l1 = List.Generate(( ) => #"Changed Type"{0}[X], each _ <= #"Changed Type"{1}[X], each _ + 200 ),
    l2 = List.Generate(( ) => #"Changed Type"{0}[Y], each _ <= #"Changed Type"{1}[Y], each _ + 200 ),
    #"Converted to Table" = Table.FromList(l1, Splitter.SplitByNothing(), {"X"}, null, ExtraValues.Error),
    #"Added Y lists" = Table.AddColumn(#"Converted to Table", "Y", each l2),
    #"Expanded Y" = Table.ExpandListColumn(#"Added Y lists", "Y"),
    #"Added Values" = Table.AddColumn(#"Expanded Y", "Values", each [X]),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Values", {{"Y", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Added Values", {{"Y", type text}}, "en-GB")[Y]), "Y", "Values")
in
    #"Pivoted Column"

Output table:

Under 'Expanded Y' step in the query you should get the second table you mentioned in your post:

Hope it helps!

[Anonymous · Accepted solution]

Hi @samahiji ,

Thanks for wini_R's reply!
And @samahiji , you can also try this:
First table:

let
    MinX = 1000,
    MaxX = 4000,
    StepX = 50,
    
    MinY = 9000,
    MaxY = 51000,
    StepY = 50,
    
    XList = List.Generate(() => MinX, each _ <= MaxX, each _ + StepX),
    YList = List.Generate(() => MinY, each _ <= MaxY, each _ + StepY),
    InitialTable = Table.FromList(YList, Splitter.SplitByNothing(), {"Y"}),
    AddColumns = List.Transform(XList, each Table.AddColumn(InitialTable, Text.From(_), each [Y])),
    MergedTable = List.Accumulate(AddColumns, Table.FromRecords({}), (state, current) => if Table.RowCount(state) = 0 then current else Table.Join(state, "Y", current, "Y")),
    FinalTable = Table.RemoveColumns(MergedTable, {"Y"})
in
    FinalTable

Output:

Second table:

let
    MinX = 1000,
    MaxX = 5000,
    MinY = 9000,
    MaxY = 60000,

    XList = List.Generate(() => MinX, each _ <= MaxX, each _ + 50),
    YList = List.Generate(() => MinY, each _ <= MaxY, each _ + 50),
    CombinationList = List.Combine(List.Transform(YList, each List.Transform(XList, (X) => [X=X, Y=_]))),
    CombinationTable = Table.FromRecords(CombinationList)
in
    CombinationTable

Output:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wini_R · Accepted solution]

Hi @samahiji,

I'm not sure if I understood your requirements right but possibly this approach could be useful for you or you can adjust it to your needs. Please paste the code below into Advanced editor in Power Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3MU9JRMjQwMABSliAqVgcomlgB5JpCRM0MYMIumWlpQAETiLgpWFdsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#" " = _t, X = _t, Y = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"X", Int64.Type}, {"Y", Int64.Type}}),
    l1 = List.Generate(( ) => #"Changed Type"{0}[X], each _ <= #"Changed Type"{1}[X], each _ + 200 ),
    l2 = List.Generate(( ) => #"Changed Type"{0}[Y], each _ <= #"Changed Type"{1}[Y], each _ + 200 ),
    #"Converted to Table" = Table.FromList(l1, Splitter.SplitByNothing(), {"X"}, null, ExtraValues.Error),
    #"Added Y lists" = Table.AddColumn(#"Converted to Table", "Y", each l2),
    #"Expanded Y" = Table.ExpandListColumn(#"Added Y lists", "Y"),
    #"Added Values" = Table.AddColumn(#"Expanded Y", "Values", each [X]),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Values", {{"Y", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Added Values", {{"Y", type text}}, "en-GB")[Y]), "Y", "Values")
in
    #"Pivoted Column"

Output table:

Under 'Expanded Y' step in the query you should get the second table you mentioned in your post:

Hope it helps!