Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
hellodazza
Helper III
Helper III

Adding new rows with values from other rows

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 NameDevice CountDevice ModeGroup Type
31 Aug 2024 23_31.csv6532ONPL
31 Aug 2024 23_31.csv28336ONSports
31 Aug 2024 23_31.csv145393ONALL
31 Aug 2024 23_31.csv110525ONOthers
31 Aug 2024 23_31.csv9560StandbySports
31 Aug 2024 23_31.csv1638StandbyPL
31 Aug 2024 23_31.csv54967StandbyALL
31 Aug 2024 23_31.csv43769StandbyOthers
3 ACCEPTED SOLUTIONS
AlienSx
Super User
Super User

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

View solution in original post

slorin
Super User
Super User

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 

View solution in original post

dufoq3
Super User
Super User

Hi @hellodazza, another one:

 

Output:

dufoq3_0-1725889894411.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

4 REPLIES 4
Omid_Motamedise
Super User
Super User

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

If my answer helped solve your issue, please consider marking it as the accepted solution. It helps others in the community find answers faster—and keeps the community growing stronger!
You can also check out my YouTube channel for tutorials, tips, and real-world solutions in Power Query with the following link
https://youtube.com/@omidbi?si=96Bo-ZsSwOx0Z36h
dufoq3
Super User
Super User

Hi @hellodazza, another one:

 

Output:

dufoq3_0-1725889894411.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

slorin
Super User
Super User

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
Super User
Super User

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.