Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I need to make a subtraction between dates, selecting the previous one from another column. I tried using the EARLIER function, but without success.
Returns the following error:
EARLIER/EARLIEST references a previous line context that does not exist.
let
Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKL0ktyS9SKMksyFdIzs9LzkhU0lEyMNQHIiMDQwtkjiWQY6gUq4NTm7GBviFcpZElhGNkQEgbzAIjQ2SOEdHajJA5xgQdaahvaATTBucQ1AaywAyk0gSZY0q+bUbk2QbUFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EPI = _t, #"Data Entrega" = _t, #"Data de troca" = _t, Empregado = _t]),
#"Tipo Alterado" = Table.TransformColumnTypes(Fonte,{{"EPI", type text}, {"Data Entrega", type date}, {"Data de troca", type date}})
in
#"Tipo Alterado"
Solved! Go to Solution.
Yes , sorry for the oversight. There you go:
let
Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKL0ktyS9SKMksyFdIzs9LzkhU0lEyMNQHIiMDQwtkjiWQY6gUq4NTm7GBviFcpZElhGNkQEgbzAIjQ2SOEdHajJA5xgQdaahvaATTBucQ1AaywAyk0gSZY0q+bUbk2QbUFgsA", BinaryEncoding.Base64), Compression.Deflate)),
let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EPI = _t, #"Data Entrega" = _t, #"Data de troca" = _t, Empregado = _t]),
AddedIndex = Table.AddIndexColumn(Fonte, "Index", 1, 1, Int64.Type),
CalculateLag = Table.AddColumn(AddedIndex, "Lag", each
if [Index] > 1 and [Empregado] = AddedIndex{[Index]-2}[Empregado] then
AddedIndex{[Index]-2}[Data de troca]
else null),
#"Changed Type with Locale" = Table.TransformColumnTypes(CalculateLag, {{"Data Entrega", type date}, {"Data de troca", type date}, {"Lag", type date}}, "es-ES"),
Subtraction = Table.AddColumn(#"Changed Type with Locale", "Subtraction", each [Data Entrega] - [Lag]),
#"Changed Type" = Table.TransformColumnTypes(Subtraction, {{"Subtraction", Int64.Type}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"EPI", "Data Entrega", "Data de troca", "Empregado", "Subtraction"})
in
#"Removed Other Columns"
I also kept original Index in the M code.
You can try using this code for power query that calculate Lag of column using indexing.
Here are the steps applied:
1. added index column
2. calculate lag using index
3. replace errors with null because first value cannot be calculated
4 changed format to date (using locale spanish)
5 subtracting one date from another in new column 'subtraction'
6 removing uneccesary columns
let
Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKL0ktyS9SKMksyFdIzs9LzkhU0lEyMNQHIiMDQwtkjiWQY6gUq4NTm7GBviFcpZElhGNkQEgbzAIjQ2SOEdHajJA5xgQdaahvaATTBucQ1AaywAyk0gSZY0q+bUbk2QbUFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EPI = _t, #"Data Entrega" = _t, #"Data de troca" = _t, Empregado = _t]),
AddedIndex = Table.AddIndexColumn(Fonte, "Index", 1, 1, Int64.Type),
CalculateLag = Table.AddColumn(AddedIndex, "Lag", each AddedIndex{[Index]-2}[Data de troca]),
#"Replaced Errors" = Table.ReplaceErrorValues(CalculateLag, {{"Lag", null}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Replaced Errors", {{"Data Entrega", type date}, {"Data de troca", type date}, {"Lag", type date}}, "es-ES"),
Subtraction = Table.AddColumn(#"Changed Type with Locale", "Subtraction", each [Data Entrega] - [Lag]),
#"Changed Type" = Table.TransformColumnTypes(Subtraction,{{"Subtraction", Int64.Type}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"EPI", "Data Entrega", "Data de troca", "Subtraction"})
in
#"Removed Other Columns"
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn if you would like to connect: www.linkedin.com/in/lukasz-kozdron
Thank you very much for your help, but I would like the calculation to only consider within the same ID.
Yes , sorry for the oversight. There you go:
let
Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKL0ktyS9SKMksyFdIzs9LzkhU0lEyMNQHIiMDQwtkjiWQY6gUq4NTm7GBviFcpZElhGNkQEgbzAIjQ2SOEdHajJA5xgQdaahvaATTBucQ1AaywAyk0gSZY0q+bUbk2QbUFgsA", BinaryEncoding.Base64), Compression.Deflate)),
let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EPI = _t, #"Data Entrega" = _t, #"Data de troca" = _t, Empregado = _t]),
AddedIndex = Table.AddIndexColumn(Fonte, "Index", 1, 1, Int64.Type),
CalculateLag = Table.AddColumn(AddedIndex, "Lag", each
if [Index] > 1 and [Empregado] = AddedIndex{[Index]-2}[Empregado] then
AddedIndex{[Index]-2}[Data de troca]
else null),
#"Changed Type with Locale" = Table.TransformColumnTypes(CalculateLag, {{"Data Entrega", type date}, {"Data de troca", type date}, {"Lag", type date}}, "es-ES"),
Subtraction = Table.AddColumn(#"Changed Type with Locale", "Subtraction", each [Data Entrega] - [Lag]),
#"Changed Type" = Table.TransformColumnTypes(Subtraction, {{"Subtraction", Int64.Type}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"EPI", "Data Entrega", "Data de troca", "Empregado", "Subtraction"})
in
#"Removed Other Columns"
I also kept original Index in the M code.
Hello @StrategicSavvy , sorry to bother you again. But how would I insert this same rule into the grouped table?
let
Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZFNCsIwEEavUrIuZGaiYg8huC9dlCDoxkjMhTyLFzNtaQm0kx8qZPOFeXzDvLYVCCBqcbXG3ZyxlXu8TKXNU997/w0o/SPAcxgaH1B0dQJWIHGZp2YKBHnwXEYYBiqEKQwqc22USDO8hEx4KDsN84cwHPc2057mCaYRvnw/b93bHLcrIOWTbeAc8gDjbb1SwtVmQ8xPQUPkrH/yoEo9MADvIQWsPHArsVeKNGxfyQPdDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"COD EPI" = _t, EPI = _t, #"Data Entrega" = _t, #"Data de troca" = _t, Empregado = _t]),
#"Linhas Classificadas" = Table.Sort(Fonte,{{"Empregado", Order.Ascending}}),
#"Linhas Agrupadas" = Table.Group(#"Linhas Classificadas", {"COD EPI", "Empregado"}, {{"Contagem", each Table.AddIndexColumn(_,"Index",1,1)}})
in
#"Linhas Agrupadas"
I assumed you would like to sum the results. by employeer and EPI code. It required extra steps with sorting as well. There you go :
let
Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZFNCsIwEEavUrIuZGaiYg8huC9dlCDoxkjMhTyLFzNtaQm0kx8qZPOFeXzDvLYVCCBqcbXG3ZyxlXu8TKXNU997/w0o/SPAcxgaH1B0dQJWIHGZp2YKBHnwXEYYBiqEKQwqc22USDO8hEx4KDsN84cwHPc2057mCaYRvnw/b93bHLcrIOWTbeAc8gDjbb1SwtVmQ8xPQUPkrH/yoEo9MADvIQWsPHArsVeKNGxfyQPdDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"COD EPI" = _t, EPI = _t, #"Data Entrega" = _t, #"Data de troca" = _t, Empregado = _t]),
#"Sorted Rows" = Table.Sort(Fonte,{{"Empregado", Order.Ascending}}),
AddedIndex = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
CalculateLag = Table.AddColumn(AddedIndex, "Lag", each
if [Index] > 1 and [Empregado] = AddedIndex{[Index]-2}[Empregado] then
AddedIndex{[Index]-2}[Data de troca]
else null),
#"Linhas Classificadas" = Table.Sort(CalculateLag,{{"Empregado", Order.Ascending}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Linhas Classificadas", {{"Data Entrega", type date}, {"Lag", type date}}, "es-ES"),
#"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each [Data Entrega] - [Lag]),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Custom", "Subtraction"}}),
#"Grouped Rows" = Table.Group(#"Renamed Columns", {"COD EPI", "Empregado"}, {{"Contagem", each List.Sum([Subtraction]), type nullable number}})
in
#"Grouped Rows"
My question is how to do "AddedIndex{[Index]-2}[Exchange Date]" within Table.Group
May be in the next step:
Table.Group(#"Linhas Classificadas", {"COD EPI", "Empregado"}, {{"Contagem", each Table.AddIndexColumn(_,"Index",1,1)}})
let
Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZFNCsIwEEavUrIuZGaiYg8huC9dlCDoxkjMhTyLFzNtaQm0kx8qZPOFeXzDvLYVCCBqcbXG3ZyxlXu8TKXNU997/w0o/SPAcxgaH1B0dQJWIHGZp2YKBHnwXEYYBiqEKQwqc22USDO8hEx4KDsN84cwHPc2057mCaYRvnw/b93bHLcrIOWTbeAc8gDjbb1SwtVmQ8xPQUPkrH/yoEo9MADvIQWsPHArsVeKNGxfyQPdDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"COD EPI" = _t, EPI = _t, #"Data Entrega" = _t, #"Data de troca" = _t, Empregado = _t]),
#"Linhas Classificadas" = Table.Sort(Fonte,{{"Empregado", Order.Ascending}}),
#"Linhas Agrupadas" = Table.Group(#"Linhas Classificadas", {"COD EPI", "Empregado"}, {{"Contagem", each Table.AddIndexColumn(_,"Index",1,1)}})
in
#"Linhas Agrupadas"
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
108 | |
101 | |
93 | |
69 |
User | Count |
---|---|
172 | |
135 | |
132 | |
101 | |
95 |