Best approach for referring Base Sizes related to columns of a Table

[Anonymous]

I have a table that looks like this:

Val      |  Pos |  Agg1   |  Agg2  |SGroup1 |SGroup2 |SGroup3 |SGroup4| SGroup5
0        |  1	|  0.122  | 0.210  |  0.905 |  0.221 |  0.111 | 0.993 |  0.222
0        |  2	|  0.424  | 0.530  |  0.890 |  0.328 |  0.190 | 0.964 |  0.434
0        |  3	|  0.236  | 0.789  |  0.450 |  0.656 |  0.315 | 0.465 |  0.762
1        |  4	|  0.568  | 0.331  |  0.661 |  0.770 |  0.428 | 0.747 |  0.883
Base     | null   |  700     | 200     |  320    |  140    |   360   |  450   |   500

As can be seen, it has the last row showing the Base sizes for all the columns. I want to know what is the best approach to reference these Bases sizes for each SGroup column or add these a separate column (s) in the table.

1. Do i need to remove this row as a separate single-column table query? OR
2. Do i add Base size columns for each sGroup column? OR 
3. Is there a way to Pivot / Unpivot this data (including Base Size) so that the table looks properly organized?
4. Eventually, i want to export the Final Table (with Base sizes included) into an Access table or save as an ADO recordset. How do i export it as an MSAccess or ADO recordset?

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

You can unpivot columns, then merge queries.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NdA7DsMgEIThu1BbEZ59JHWuYVG4SGeliXL/sMOkYD8E0i/BcbTetrbP1W87UHsJadJlyKRjWwWw4HAWlpAmXYZM+i8YC7BkYQlp0mXIpFWoFzgLkQ8WlpAmXYZMWoXn+XnNg/f3uib3zldxGvhHzn3W9KgZ83aMHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Val = _t, Pos = _t, Agg1 = _t, Agg2 = _t, SGroup1 = _t, SGroup2 = _t, SGroup3 = _t, SGroup4 = _t, SGroup5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Val", type text}, {"Pos", Int64.Type}, {"Agg1", type number}, {"Agg2", type number}, {"SGroup1", type number}, {"SGroup2", type number}, {"SGroup3", type number}, {"SGroup4", type number}, {"SGroup5", type number}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Val", "Pos", "Agg1", "Agg2"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Val] <> "Base")),
    #"Filtered Rows2" = Table.SelectRows(#"Unpivoted Columns", each ([Val] = "Base")),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"Attribute"}, #"Filtered Rows2", {"Attribute"}, "Base value", JoinKind.LeftOuter),
    #"Expanded Base value" = Table.ExpandTableColumn(#"Merged Queries", "Base value", {"Value"}, {"Base value.Value"})
in
    #"Expanded Base value"

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.