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 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:
Solved! Go to Solution.
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
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"
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
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"
It did work!!! Thanks a lot 😉
You are almost there, you can go with else null, then FillUp column [ID Data Fiscal].
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 | |
48 | |
19 | |
13 | |
11 |