Adding new rows with values from other rows

[hellodazza]

hi there, 

I would like to add the rows in red into my existing data. Is there a code for me to generate this inside power query? So it will generate new rows for new data that comes in automatically? Thanks.

The values are from :  ON ALL - (ON PL + ON Sports) = ON Others
The values are from :  Standby ALL - (Standby PL + Standby Sports) = Standby Others

File Name	Device Count	Device Mode	Group Type
31 Aug 2024 23_31.csv	6532	ON	PL
31 Aug 2024 23_31.csv	28336	ON	Sports
31 Aug 2024 23_31.csv	145393	ON	ALL
31 Aug 2024 23_31.csv	110525	ON	Others
31 Aug 2024 23_31.csv	9560	Standby	Sports
31 Aug 2024 23_31.csv	1638	Standby	PL
31 Aug 2024 23_31.csv	54967	Standby	ALL
31 Aug 2024 23_31.csv	43769	Standby	Others

[AlienSx · Accepted solution]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 
    f = (tbl) => 
        [rec = Record.FromList(tbl[Device Count], tbl[Group Type]),
        res = tbl & #table({"Device Count", "Group Type"}, {{List.Sum({rec[ALL], - rec[PL], -rec[Sports]}), "Other"}})][res], 
    group = Table.Group(Source, {"File Name", "Device Mode"}, {"x", f}),
    xpand = Table.ExpandTableColumn(group, "x", {"Device Count", "Group Type"})
in
    xpand

[slorin · Accepted solution]

Hi @hellodazza 

Another solution

let
Source = Your_Source,
Pivot = Table.Pivot(Source, List.Distinct(Source[#"Group Type"]), "Group Type", "Device Count"),
Others = Table.AddColumn(Pivot, "Others", each [ALL]-[Sports]-[PL]),
UnPivot = Table.UnpivotOtherColumns(Others, {"File Name", "Device Mode"}, "Group Type", "Device Count")
in
UnPivot

EDIT

with new group type

let
Source = Excel.CurrentWorkbook(){[Name = "Tableau1"]}[Content], 
Group_Type = List.Distinct(Source[Group Type]), 
Pivot = Table.Pivot(Source, Group_Type, "Group Type", "Device Count"), 
Others = Table.AddColumn(Pivot, "Others", 
    (x) => x[ALL] 
        - List.Sum(List.Transform(
               List.Select(Group_Type, each _ <> "ALL"),
               each Record.Field(x, _)))), 
UnPivot = Table.UnpivotOtherColumns(Others, {"File Name", "Device Mode"}, "Group Type", "Device Count")
in
UnPivot

Stéphane 

[Omid_Motamedise]

if the device number have specific patterns, you can add all of them once, otherwise use Table.InsertRows

[dufoq3 · Accepted solution]

Hi @hellodazza, another one:

Output:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjZUcCxNVzAyMDJRMDKONzbUSy4uU9JRMjM1NgJS/n5AIsBHKVYHt1IjC2NjM5ja4IL8opJivOoNTUyNLY1hGhx98JtuaWpmADK3JDEvJamSSBvMjC1Q9BDwgamJpZk5igawq2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"File Name" = _t, #"Device Count" = _t, #"Device Mode" = _t, #"Group Type" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Device Count", type number}}),
    GroupedRows = Table.Group(ChangedType, {"File Name", "Device Mode"}, {{"InsertedRow", each 
        [ a = List.Sum(Table.SelectRows(_, (x)=> x[Group Type] = "ALL")[Device Count]), //All value
          b = List.Sum(Table.SelectRows(_, (x)=> x[Group Type] <> "ALL")[Device Count]), //Others value
          c = a - b, //All minus Others
          d = Table.InsertRows(_, Table.RowCount(_), { _{0} & [Group Type = "Others", Device Count = c] })
        ][d], type table}}),
    Combined = Table.Combine(GroupedRows[InsertedRow])
in
    Combined

[slorin · Accepted solution]

Hi @hellodazza 

Another solution

let
Source = Your_Source,
Pivot = Table.Pivot(Source, List.Distinct(Source[#"Group Type"]), "Group Type", "Device Count"),
Others = Table.AddColumn(Pivot, "Others", each [ALL]-[Sports]-[PL]),
UnPivot = Table.UnpivotOtherColumns(Others, {"File Name", "Device Mode"}, "Group Type", "Device Count")
in
UnPivot

EDIT

with new group type

let
Source = Excel.CurrentWorkbook(){[Name = "Tableau1"]}[Content], 
Group_Type = List.Distinct(Source[Group Type]), 
Pivot = Table.Pivot(Source, Group_Type, "Group Type", "Device Count"), 
Others = Table.AddColumn(Pivot, "Others", 
    (x) => x[ALL] 
        - List.Sum(List.Transform(
               List.Select(Group_Type, each _ <> "ALL"),
               each Record.Field(x, _)))), 
UnPivot = Table.UnpivotOtherColumns(Others, {"File Name", "Device Mode"}, "Group Type", "Device Count")
in
UnPivot

Stéphane 

[AlienSx · Accepted solution]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 
    f = (tbl) => 
        [rec = Record.FromList(tbl[Device Count], tbl[Group Type]),
        res = tbl & #table({"Device Count", "Group Type"}, {{List.Sum({rec[ALL], - rec[PL], -rec[Sports]}), "Other"}})][res], 
    group = Table.Group(Source, {"File Name", "Device Mode"}, {"x", f}),
    xpand = Table.ExpandTableColumn(group, "x", {"Device Count", "Group Type"})
in
    xpand