cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EvertonRosa
Frequent Visitor

Get next line from Related Table (Table.)

I need a little help to write a Power Query function in order to get the next line (id) from a related table.

 

Actual code is:

let data = Table.Column(dCalendarA, "Month/Year") in
if [FiscalPeriod] = null then null
else if [FiscalPeriod] = "Actual" then [dCalendarA.CloseDate]
else data + 1)

Problem:

Variable data is returning month + 1, but what i do need is to return next line register.

Example:
Table dCalendarA
id Month/Year CloseDate
1  012021         29/01/2021
2  022021         26/02/2021

So in concrete case, ELSE (data + 1) must return 26/02/2021.

Data Image:

EvertonRosa_0-1627415855424.png

 

@Vera_33 @Jakinta ?

1 ACCEPTED SOLUTION
Vera_33
Super User
Super User

Hi @EvertonRosa 

 

I don't see your original code working...based on my understanding, you have a table like the screenshot, another table called dCalendarA. While you fetch the dCalendarA.CloseDate, you are looking at Month/Year in both tables to match? I modified your code a little bit, let me know if you want something else

Vera_33_0-1627434848751.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjy0QElH6dACI0t9A0N9IwMjQyDXwBDEAMs4lpQm5ijF6oyqHLwqA4ryKzJz80lWawRTa6ZvYARXa4TFBdRTSYr9cLWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, DataFechamento = _t, #"Month/Year" = _t, FiscalPeriod = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"DataFechamento", type date}, {"Month/Year", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
let data = Table.Column(dCalendarA, "Month/Year") in
if [FiscalPeriod] = null then null
else if [FiscalPeriod] = "Atual" then dCalendarA[CloseDate]{List.PositionOf(data, [#"Month/Year"])}
else try dCalendarA[CloseDate]{List.PositionOf(data, [#"Month/Year"])+1} otherwise null)
in
    #"Added Custom"

 

View solution in original post

3 REPLIES 3
Vera_33
Super User
Super User

Hi @EvertonRosa 

 

I don't see your original code working...based on my understanding, you have a table like the screenshot, another table called dCalendarA. While you fetch the dCalendarA.CloseDate, you are looking at Month/Year in both tables to match? I modified your code a little bit, let me know if you want something else

Vera_33_0-1627434848751.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjy0QElH6dACI0t9A0N9IwMjQyDXwBDEAMs4lpQm5ijF6oyqHLwqA4ryKzJz80lWawRTa6ZvYARXa4TFBdRTSYr9cLWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, DataFechamento = _t, #"Month/Year" = _t, FiscalPeriod = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"DataFechamento", type date}, {"Month/Year", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
let data = Table.Column(dCalendarA, "Month/Year") in
if [FiscalPeriod] = null then null
else if [FiscalPeriod] = "Atual" then dCalendarA[CloseDate]{List.PositionOf(data, [#"Month/Year"])}
else try dCalendarA[CloseDate]{List.PositionOf(data, [#"Month/Year"])+1} otherwise null)
in
    #"Added Custom"

 

View solution in original post

It did work!!! Thanks a lot 😉

Jakinta
Super User
Super User

You are almost there, you can go with else null, then FillUp column [ID Data Fiscal].

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Kudoed Authors