CODEWITHSUNDEEP
pivotpowerqueryunpivot
Martin
Martin

Reputation: 409

Add appended columns in PowerQuery

I have the following dataset:

lookup                              criterion   s[m]    Ns[MN]      My[MNm]     Mz[MNm]     Vy[MN]      Vz[MN]      Mt[MNm]     part
1000max NSga1EPSel1sl1se1pa6002     max NS      131     -29.8787    2.4734      4.7149      0.2146      0.9537      0.0439      6002
1000min MYga1EPSel1sl1se1pa6002     min MY      131     -42.2365    -38.9471    7.0569      0.0571      -4.3567     0.2086      6002
1000max NSga1EPSel1sl1se1pa6003     max NS      182     -4.5598     -3.5734     0.6512      -0.1213     0           -0.1213     6003.1
1000min MYga1EPSel1sl1se1pa6003     min MY      182     -11.4483    -10.314     4.3061      -0.2013     0           -0.2013     6003.1
1000max NSga1EPSel1sl1se1pa6003     max NS      0       -5.0888     4.0277      2.4967      0.1602      0           -0.1602     6003.2
1000min MYga1EPSel1sl1se1pa6003     min MY      0       -5.0888     4.0277      2.4967      0.1602      0           -0.1602     6003.2
1000max NSga1EPSel1sl1se1pa6004     max NS      36      -11.4002    0           -0.7057     -0.02       0           0           6004
1000min MYga1EPSel1sl1se1pa6004     min MY      36      -15.2575    0           -0.4365     -0.0289     0           0           6004

I would like to add part 6003.1 and 6003.2 together so I only have 6003 and my dataset would look like this:

lookup                          criterion   s[m]    Ns[MN]      My[MNm]     Mz[MNm]     Vy[MN]      Vz[MN]      Mt[MNm]     part
1000max NSga1EPSel1sl1se1pa6002 max NS      131     -29.8787    2.4734      4.7149      0.2146      0.9537      0.0439      6002
1000min MYga1EPSel1sl1se1pa6002 min MY      131     -42.2365    -38.9471    7.0569      0.0571      -4.3567     0.2086      6002
1000max NSga1EPSel1sl1se1pa6003 max NS      182     -9.6486     0.4543      3.1479      0.0389      0           -0.2815     6003
1000min MYga1EPSel1sl1se1pa6003 min MY      182     -16.5371    -6.2863     6.8028      -0.0411     0           -0.3615     6003
1000max NSga1EPSel1sl1se1pa6004 max NS      36      -11.4002    0           -0.7057     -0.02       0           0           6004
1000min MYga1EPSel1sl1se1pa6004 min MY      36      -15.2575    0           -0.4365     -0.0289     0           0           6004

I figured it would have something to do with indexing the rows and pivot my columns and unpvivot afterwards, but I end up with a whole bunch of null rows looking like this:

lookup                              criterion   s[m]    Index   Attribute   6002        6003.1      6003.2      6004
1000max NSga1EPSel1sl1se1pa6002     max NS      131     1       Mt[MNm]     0.0439      null        null        null
1000max NSga1EPSel1sl1se1pa6002     max NS      131     1       My[MNm]     2.4734      null        null        null
1000max NSga1EPSel1sl1se1pa6002     max NS      131     1       Mz[MNm]     4.7149      null        null        null
1000max NSga1EPSel1sl1se1pa6002     max NS      131     1       Ns[MN]      -29.8787    null        null        null
1000max NSga1EPSel1sl1se1pa6002     max NS      131     1       Vy[MN]      0.2146      null        null        null
1000max NSga1EPSel1sl1se1pa6002     max NS      131     1       Vz[MN]      0.9537      null        null        null
1000max NSga1EPSel1sl1se1pa6003     max NS      0       5       Mt[MNm]     null        null        -0.1602     null
1000max NSga1EPSel1sl1se1pa6003     max NS      0       5       My[MNm]     null        null        4.0277      null
1000max NSga1EPSel1sl1se1pa6003     max NS      0       5       Mz[MNm]     null        null        2.4967      null
1000max NSga1EPSel1sl1se1pa6003     max NS      0       5       Ns[MN]      null        null        -5.0888     null
1000max NSga1EPSel1sl1se1pa6003     max NS      0       5       Vy[MN]      null        null        0.1602      null
1000max NSga1EPSel1sl1se1pa6003     max NS      0       5       Vz[MN]      null        null        0           null
1000max NSga1EPSel1sl1se1pa6003     max NS      182     3       Mt[MNm]     null        -0.1213     null        null
1000max NSga1EPSel1sl1se1pa6003     max NS      182     3       My[MNm]     null        -3.5734     null        null
1000max NSga1EPSel1sl1se1pa6003     max NS      182     3       Mz[MNm]     null        0.6512      null        null
1000max NSga1EPSel1sl1se1pa6003     max NS      182     3       Ns[MN]      null        -4.5598     null        null
1000max NSga1EPSel1sl1se1pa6003     max NS      182     3       Vy[MN]      null        -0.1213     null        null
1000max NSga1EPSel1sl1se1pa6003     max NS      182     3       Vz[MN]      null        0           null        null
1000max NSga1EPSel1sl1se1pa6004     max NS      36      7       Mt[MNm]     null        null        null        0
1000max NSga1EPSel1sl1se1pa6004     max NS      36      7       My[MNm]     null        null        null        0
1000max NSga1EPSel1sl1se1pa6004     max NS      36      7       Mz[MNm]     null        null        null        -0.7057
1000max NSga1EPSel1sl1se1pa6004     max NS      36      7       Ns[MN]      null        null        null        -11.4002
1000max NSga1EPSel1sl1se1pa6004     max NS      36      7       Vy[MN]      null        null        null        -0.02
1000max NSga1EPSel1sl1se1pa6004     max NS      36      7       Vz[MN]      null        null        null        0
1000min MYga1EPSel1sl1se1pa6002     min MY      131     2       Mt[MNm]     0.2086      null        null        null
1000min MYga1EPSel1sl1se1pa6002     min MY      131     2       My[MNm]     -38.9471    null        null        null
1000min MYga1EPSel1sl1se1pa6002     min MY      131     2       Mz[MNm]     7.0569      null        null        null
1000min MYga1EPSel1sl1se1pa6002     min MY      131     2       Ns[MN]      -42.2365    null        null        null
1000min MYga1EPSel1sl1se1pa6002     min MY      131     2       Vy[MN]      0.0571      null        null        null
1000min MYga1EPSel1sl1se1pa6002     min MY      131     2       Vz[MN]      -4.3567     null        null        null
1000min MYga1EPSel1sl1se1pa6003     min MY      0       6       Mt[MNm]     null        null        -0.1602     null
1000min MYga1EPSel1sl1se1pa6003     min MY      0       6       My[MNm]     null        null        4.0277      null
1000min MYga1EPSel1sl1se1pa6003     min MY      0       6       Mz[MNm]     null        null        2.4967      null
1000min MYga1EPSel1sl1se1pa6003     min MY      0       6       Ns[MN]      null        null        -5.0888     null
1000min MYga1EPSel1sl1se1pa6003     min MY      0       6       Vy[MN]      null        null        0.1602      null
1000min MYga1EPSel1sl1se1pa6003     min MY      0       6       Vz[MN]      null        null        0           null
1000min MYga1EPSel1sl1se1pa6003     min MY      182     4       Mt[MNm]     null        -0.2013     null        null
1000min MYga1EPSel1sl1se1pa6003     min MY      182     4       My[MNm]     null        -10.314     null        null
1000min MYga1EPSel1sl1se1pa6003     min MY      182     4       Mz[MNm]     null        4.3061      null        null
1000min MYga1EPSel1sl1se1pa6003     min MY      182     4       Ns[MN]      null        -11.4483    null        null
1000min MYga1EPSel1sl1se1pa6003     min MY      182     4       Vy[MN]      null        -0.2013     null        null
1000min MYga1EPSel1sl1se1pa6003     min MY      182     4       Vz[MN]      null        0           null        null
1000min MYga1EPSel1sl1se1pa6004     min MY      36      8       Mt[MNm]     null        null        null        0
1000min MYga1EPSel1sl1se1pa6004     min MY      36      8       My[MNm]     null        null        null        0
1000min MYga1EPSel1sl1se1pa6004     min MY      36      8       Mz[MNm]     null        null        null        -0.4365
1000min MYga1EPSel1sl1se1pa6004     min MY      36      8       Ns[MN]      null        null        null        -15.2575
1000min MYga1EPSel1sl1se1pa6004     min MY      36      8       Vy[MN]      null        null        null        -0.0289
1000min MYga1EPSel1sl1se1pa6004     min MY      36      8       Vz[MN]      null        null        null        0

So if this is actually the right way to go, how to I move all those null rows to be on the same row?

This is the code I have so far:

let
    Source = Append1,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"lookup", "criterion", "s[m]", "part", "Index"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[part]), "part", "Value", List.Sum)
in
    #"Pivoted Column"

Thank you very much

Upvotes: 0

Views: 43

Answers (1)

Olly
Olly

Reputation: 7891

First of all, identify the main part number - from your sample data, this looks like we simply round down the part number to the whole number value.

Then you can simply group by lookup, criterion and part, and sum the value columns:

let
    Source = Append1,
    #"Identify Part" = Table.TransformColumns(Source,{{"part", Number.RoundDown, Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Identify Part", {"lookup", "criterion", "part"}, {{"s[m]", each List.Sum([#"s[m]"]), type number}, {"Ns[MN]", each List.Sum([#"Ns[MN]"]), type number}, {"My[MNm]", each List.Sum([#"My[MNm]"]), type number}, {"Mz[MNm]", each List.Sum([#"Mz[MNm]"]), type number}, {"Vy[MN]", each List.Sum([#"Vy[MN]"]), type number}, {"Vz[MN]", each List.Sum([#"Vz[MN]"]), type number}, {"Mt[MNm]", each List.Sum([#"Mt[MNm]"]), type number}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"lookup", "criterion", "s[m]", "Ns[MN]", "My[MNm]", "Mz[MNm]", "Vy[MN]", "Vz[MN]", "Mt[MNm]", "part"})
in
    #"Reordered Columns"

Example PBIX file: https://pwrbi.com/so_55019712/

Upvotes: 1

Related Questions