Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I want to create a new calculated row in my table. I would like to do that in Power Query. Can anyone help, please?
I have a table that looks like this. On the last row I have the total of the above rows plus something else, which I want to categorize into "Other". So, I want to get rid of the last row, the "FULL" in Column B and I want to add a new calculated row that will be categorized into "Other" and which will be calculated as the difference between the "FULL" and the SUM between the other rows: 1135 - SUM(655+115+38+18+50+6+15)=238. Please see the screenshot below.
Any advices on how can I achieve this result?
Solved! Go to Solution.
pls try this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVtJRcgRiM1NTpVgdmIgTEBsaIos4A7GxBZKAC0gJsoArEJsaIAm4gYxF4ruDdCCb6Rbq4wO2yBgqWpyC7hqwCIprwCLIrgELILsGLIDsGrAAkmvAfGTXQBRAXGNkYADUFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t, #"Column C" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}, {"Column B", type text}, {"Column C", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Column A"}, {{"Count",
(x) =>
Table.ReplaceValue(
Table.TransformColumns(x, {"Column B", (z) => if z = "FULL" then "Other" else z}),
each [Column C],
each
if [Column B] = "Other" then
[Column C] - List.Sum(List.RemoveLastN(x[Column C], 1))
else
[Column C],
Replacer.ReplaceValue,
{"Column B", "Column C"}
)
}}),
#"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Count"}),
#"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"Column A", "Column B", "Column C"}, {"Column A", "Column B", "Column C"})
in
#"Expanded Count"
pls try this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVtJRcgRiM1NTpVgdmIgTEBsaIos4A7GxBZKAC0gJsoArEJsaIAm4gYxF4ruDdCCb6Rbq4wO2yBgqWpyC7hqwCIprwCLIrgELILsGLIDsGrAAkmvAfGTXQBRAXGNkYADUFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t, #"Column C" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}, {"Column B", type text}, {"Column C", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Column A"}, {{"Count",
(x) =>
Table.ReplaceValue(
Table.TransformColumns(x, {"Column B", (z) => if z = "FULL" then "Other" else z}),
each [Column C],
each
if [Column B] = "Other" then
[Column C] - List.Sum(List.RemoveLastN(x[Column C], 1))
else
[Column C],
Replacer.ReplaceValue,
{"Column B", "Column C"}
)
}}),
#"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Count"}),
#"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"Column A", "Column B", "Column C"}, {"Column A", "Column B", "Column C"})
in
#"Expanded Count"
or try this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVtJRcgRiM1NTpVgdmIgTEBsaIos4A7GxBZKAC0gJsoArEJsaIAm4gYxF4ruDdCCb6Rbq4wO2yBgqWpyC7hqwCIprwCLIrgELILsGLIDsGrAAkmvAfGTXQBRAXGNkYADUFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t, #"Column C" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}, {"Column B", type text}, {"Column C", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Column A"}, {{"Count",
(x) =>
Table.ReplaceValue(
x,
each [Column C],
each
if [Column B] = "FULL" then
[Column C] - List.Sum(List.RemoveLastN(x[Column C], 1))
else
[Column C],
Replacer.ReplaceValue,
{"Column B", "Column C"}
)
}}),
#"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Count"}),
#"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"Column A", "Column B", "Column C"}, {"Column A", "Column B", "Column C"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Count","FULL","Other",Replacer.ReplaceText,{"Column B"})
in
#"Replaced Value"
@Elina_Mirin , another row can not be created what can be done is making a measure which is the difference between Full and remaining values of column B.
@Elina_Mirin , I dont think calculated row can be added to a table using power query.
Do you have another idea? Can it be done in DAX? I thought maybe it's easier in Power Query.
User | Count |
---|---|
85 | |
75 | |
70 | |
69 | |
55 |
User | Count |
---|---|
98 | |
96 | |
92 | |
78 | |
70 |