Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello,
I want to have two columns: one for finding previous "REIS" value and other for next "REIS" value for those rows whose type is not "REIS" and when there are different categories.
Let's say category 12B have four "REIS" on the 9th of May. Each row after "REIS" by date should return me the previous or next row value with "REIS" type.
Idk if the explanation is clear, the attached picture shows what result I need in Previous and Next columns.
Please help.
Category | Type | Number | Date | Previous | Next |
12B | RYT | 45B | 2023-05-09 07:11 | 65T | |
12B | REIS | 65T | 2023-05-09 07:11 | ||
12B | PAS | 56T | 2023-05-09 07:56 | 65T | 56C |
12B | PAS | 35G | 2023-05-09 08:24 | 65T | 56C |
12B | REIS | 56C | 2023-05-09 08:42 | ||
12B | PAS | 43V | 2023-05-09 11:45 | 56C | 78K |
12B | REIS | 78K | 2023-05-09 13:04 | ||
12B | PAS | 52B | 2023-05-09 13:04 | 78K | 23S |
12B | PAS | 56H | 2023-05-09 13:05 | 78K | 23S |
12B | PAS | 45P | 2023-05-09 16:42 | 78K | 23S |
12B | KUR | 89L | 2023-05-09 19:52 | 78K | 23S |
12B | REIS | 23S | 2023-05-09 20:03 | ||
12F | PAS | 56H | 2023-05-09 07:56 | 89L | |
12F | PAS | 45P | 2023-05-09 08:24 | 89L | |
12F | REIS | 89L | 2023-05-09 08:42 | ||
12F | PAB | 23S | 2023-05-09 16:42 | 89L | 65W |
12F | REIS | 65W | 2023-05-09 13:05 | ||
12F | PAS | 56H | 2023-05-09 13:05 | 65W | |
12F | PAS | 45P | 2023-05-09 16:42 | 65W | |
12F | KUR | 89L | 2023-05-09 20:03 | 65W |
OK, pls again try
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZHBDoIwEER/xfRsSLvtLtCbGKsGDwRQYwj//xtCJNp2KfGy7eFldmZ2GISCSuxF++qnaXD+yzKTmIEEvcutUmLcf6nTtZsewn4Taw4zhcQoJEZpPIdUYcHwlUjHGDPAxIx+hJRS1iBXy4s64rSVhieA6i+MLhxDbg6bCKMwQ31vp1mUtwgrLQLPALoLOZBW6oVzaXPeFVzam3cF99vJvHlX+IhVa878oJ4a4TPd20aCdWyzXpeud6ltfAM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Type = _t, Number = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Type", type text}, {"Number", type text}, {"Date", type datetime}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Category"}, {{"Count",
(x)=> Table.ReplaceValue(
Table.FillDown(
Table.AddColumn(
Table.AddIndexColumn(x,"Index",1,1),"Previous", each if [Type] ="REIS" then [Number] else null),
{"Previous"}) ,each [Previous] ,each if [Type] = "REIS" then null else [Previous] ,Replacer.ReplaceValue,{"Type", "Previous"})
}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Type", "Number", "Date", "Index", "Previous"}, {"Type", "Number", "Date", "Index", "Previous"}),
#"Grouped Rows1" = Table.Group(#"Expanded Count", {"Category"},
{{"Count",
(x)=>
Table.ReplaceValue(
Table.FillUp(
Table.ReplaceValue(
Table.FillUp(Table.DuplicateColumn(x, "Previous", "Next"),{"Next"}),
each [Next] ,each if [Type] <> "REIS" then null else [Next] ,Replacer.ReplaceValue,{"Type", "Next"}),
{"Next"}),each [Next] ,each if [Type] = "REIS" then null else [Next] ,Replacer.ReplaceValue,{"Type", "Next"})
}}),
#"Removed Other Columns" = Table.Combine( Table.SelectColumns(#"Grouped Rows1",{"Count"})[Count])
in
#"Removed Other Columns"
plse try this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZHBDoIwEER/xfRsSLvtLtCbGKsGDwRQYwj//xtCJNp2KfGy7eFldmZ2GISCSuxF++qnaXD+yzKTmIEEvcutUmLcf6nTtZsewn4Taw4zhcQoJEZpPIdUYcHwlUjHGDPAxIx+hJRS1iBXy4s64rSVhieA6i+MLhxDbg6bCKMwQ31vp1mUtwgrLQLPALoLOZBW6oVzaXPeFVzam3cF99vJvHlX+IhVa878oJ4a4TPd20aCdWyzXpeud6ltfAM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Type = _t, Number = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Type", type text}, {"Number", type text}, {"Date", type datetime}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "step1", each if [Type] ="REIS" then [Number] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"step1"}),
from1 = Table.AddColumn(#"Filled Down", "Previous", each if [Type] = "REIS" then null else [step1]),
#"Removed Columns" = Table.RemoveColumns(from1,{"step1"}),
from2 = Table.AddColumn(#"Removed Columns", "N", each [Previous]),
from3 = Table.FillUp(from2,{"N"}),
#"Added Custom1" = Table.AddColumn(from3, "Custom", each [ t =
Text.Combine(
List.Range( from3[N] &{null},[Index],1)),
to= if [Type] = "REIS" then null else if [Previous] =null or [N]<> t then t else null][to]),
#"Filled Up" = Table.FillUp(#"Added Custom1",{"Custom"}),
#"Added Custom2" = Table.AddColumn(#"Filled Up", "Next", each if [Type] = "REIS" then null else [Custom]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Index", "N", "Custom"})
in
#"Removed Columns1"
Hi @Ahmedx thank you so much for your response.
It almost works, it just ignores Category. It should return values from the same Category, could you please help with that?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
85 | |
84 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
97 | |
71 | |
67 |