Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to 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
Hi @Anonymous
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
Hi @Anonymous, 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
if the device number have specific patterns, you can add all of them once, otherwise use Table.InsertRows
Hi @Anonymous, 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
Hi @Anonymous
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
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
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.