Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I want to add a custom column with the value from column 'message', if that next row exists, for else I get an error.
Now I have:
Table.AddColumn(#"Added Index", "next", each if #"Added Index"{[Index]+1} then #"Added Index"{[Index]+1}[message]
else null)
But this gives errors for all rows.
What am I doing wrong?
Solved! Go to Solution.
Table.AddColumn(#"Added Index", "next", each let a=#"Added Index"{[Index]+1}? in if a=null then null else a[message])
Table.AddColumn(#"Added Index", "next", each let a=#"Added Index"{[Index]+1}? in if a=null then null else a[message])
Are you trying to get the value of the next row in column [Message]?
Yes, but only if a next row exists can I get a value.
For if the next row does not exist I get an error using my code.
Hello - there is a great custom function to retreive the values of next/previous rows by Imke Feldmann.
RESULT (Data table)
I added two columns as an example - one for getting data from the next row and another for getting data from a previous row.
SCRIPT fnGetDifferentRow
let func =
(Table as table, optional Step as number, optional SelectedColumns, optional GroupByColumns, optional Suffix as text, optional Buffer as any) =>
let
// Steps to prepare the (optional) parameters for the nested function "fnFetchNextRow"
Source = if Buffer = null then Table else Table.Buffer(Table),
Step0 = if Step = null then -1 else Step,
Step_ = if Step = null then 1 else Number.Abs(Step),
Suffix = if Suffix = null then ".Prev" else Suffix,
GroupByColumns = if GroupByColumns = null then null else GroupByColumns,
ShiftFunction = if Step0 < 0 then Table.RemoveLastN else Table.RemoveFirstN,
ColNames = List.Buffer(Table.ColumnNames(Source)),
NewColNames = if SelectedColumns = null then ColNames else SelectedColumns,
CountNewCols = List.Count(NewColNames),
// Core function that retrieves values from previous or next rows (depending on sign of parameter "Step")
fnFetchNextRow = (Table_ as table, optional Step as number, optional SelectedColumns, optional Suffix as text, optional Buffer as any) =>
let
MergeTable = if SelectedColumns = null then Table_ else Table.SelectColumns(Table_, SelectedColumns),
Shift = if Step0 > 0 then ShiftFunction(MergeTable, Step_) & #table(NewColNames, List.Repeat({List.Repeat({null}, CountNewCols)}, Step_))
else #table(NewColNames, List.Repeat({List.Repeat({null}, CountNewCols)}, Step_)) & ShiftFunction(MergeTable, Step_),
Reassemble = Table.ToColumns(Table_) & Table.ToColumns(Shift),
Custom1 = Table.FromColumns( Reassemble, Table.ColumnNames(Source) & List.Transform(NewColNames, each _&Suffix ) )
in
Custom1,
// optional grouping on certain columns
#"Grouped Rows" = Table.Group(Source, GroupByColumns, {{"All", each _}}, GroupKind.Local),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each fnFetchNextRow([All], Step0, SelectedColumns, Suffix, Buffer)),
#"Removed Columns" = Table.Combine(Table.RemoveColumns(#"Added Custom", GroupByColumns & {"All"})[Custom]),
// case no grouping
NoGroup = fnFetchNextRow(Source, Step0, SelectedColumns, Suffix, Buffer),
// select case grouping
Result = if GroupByColumns = null then NoGroup else #"Removed Columns"
in
Result ,
documentation = [
Documentation.Name = " Table.ReferenceDifferentRow ",
Documentation.Description = " Adds columns to a <code>Table</code> with values from previous or next rows (according to the <code>Step</code>-index in the 2nd parameter) ",
Documentation.LongDescription = " Adds columns to a <code>Table</code> with values from previous or next rows (according to the <code>Step</code>-index in the 2nd parameter) ",
Documentation.Category = " Table ",
Documentation.Source = " ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Imke Feldmann (www.TheBIccountant.com ) ",
Documentation.Examples = {[Description = " ",
Code = " fnTable_ReferenceDifferentRow( #table( {""Product"", ""Value""}, List.Zip( { {""A"" ,""A"" ,""B"" ,""B"" ,""B""}, {""1"" ,""2"" ,""3"" ,""4"" ,""5""} } ) ) ) ",
Result = " #table( {""Product"", ""Value"", ""Product.Prev"", ""Value.Prev""}, List.Zip( { {""A"" ,""A"" ,""B"" ,""B"" ,""B""}, {""1"" ,""2"" ,""3"" ,""4"" ,""5""}, {null ,""A"" ,""A"" ,""B"" ,""B""}, {null ,""1"" ,""2"" ,""3"" ,""4""} } ) ) "]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))
SCRIPT Data
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyElV0lEyVIrViVZKSswDQiDXCMwtSE1MzgDyjMG84pKixPKk1KKiSqCQCVgovSixAKTbVCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruit = _t, Quantity = _t]),
ChangeType = Table.TransformColumnTypes(Source,{{"Fruit", type text}, {"Quantity", Int64.Type}}),
// Source of Custom Function: https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-power-query-or-power-bi/
AddNextRowValue = fnGetDifferentRow(
ChangeType, // table to transform
1, // 1 = get value from the next row
{"Fruit"}, // list of columns to add with next row values
null, // list of columns by which the data should be grouped
".next", // suffix to add to the end of the new column names
null // buffer option
),
AddPrevRowValue = fnGetDifferentRow(
AddNextRowValue,
null, // null/default = get value from the previous row
{"Fruit"}, // list of columns to add with next row values
null, // list of columns by which the data should be grouped
".prev", // suffix to add to the end of the new column names
null // buffer option
)
in
AddPrevRowValue
Thanks for the solution for getting values if the next row exists.
Not my question, but nice to have for another time!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |