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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
hellodazza
Helper II
Helper II

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

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors
Top Kudoed Authors