Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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 @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
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
if the device number have specific patterns, you can add all of them once, otherwise use Table.InsertRows
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
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
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
17 | |
9 | |
8 | |
7 | |
7 |