Forum Discussion
Excel Power Query value with leading space cannot covert
Below is my code in Power Query within Excel to compare the next value to the current value. = Table.AddColumn(#"Added Index", "Original Contract2", each if [Order Group Primary Number] {[Index]...
You need to reference entire column with numbers, not each element in it. That could be as
#"Added Custom" = Table.AddColumn(
#"Added Index",
"Custom",
each
if #"Added Index"[Order Group Primary Number]{[Index] + 1} =
#"Added Index"[Order Group Primary Number]{[Index]}
then null
else [Original Contract]
)or
#"Added Custom" = Table.AddColumn(
#"Added Index",
"Custom",
each [numbers= #"Added Index"[Order Group Primary Number],
a = if #"Added Index"[Order Group Primary Number]{[Index] + 1} =
#"Added Index"[Order Group Primary Number]{[Index]}
then null
else [Original Contract]
][a]
)or like.
Alternatively you may add another Index (first starts from zero, another from 1), merge table with itself on these indexes, extract field with primary number and add conditional column.
Sorry, second variant is
#"Added Custom" = Table.AddColumn(
#"Added Index",
"Custom",
each [numbers= #"Added Index"[Order Group Primary Number],
a = if numbers{[Index] + 1} =
numbers{[Index]}
then null
else [Original Contract]
][a]
)
- SergeiBaklan I am using the second variant of the code. I am now getting an "Index was outside the bounds of the array." Is there a way to validate that the index list has reached its max before adding "1"?
If I remember the logic correctly latest in the group record always has Original Contract, all other are null. With that you may modify the script as
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type), rows = Table.RowCount(#"Added Index"), // be sure Index starts from zero or change formula #"Added Custom" = Table.AddColumn( #"Added Index", "Custom", each if [Index] < rows-1 and #"Added Index"[Order Group Primary Number]{[Index] + 1} = #"Added Index"[Order Group Primary Number]{[Index]} then null else [Original Contract] )Here we get number of rows in the table and check or we on last record or not.
- SergeiBaklan Thank you! The updated code takes more than 10 minutes, so I eventually kill it. I'll keep working with it. Thanks for all your help!
- Hi,
Add statement try after each and otherwise null before last )
Artur- artup720 Thanks! I am not successful in adding the try statement. The result is that it is always in error. I'm still playing with it.
SergeiBaklan Thank you for both variants to resolve my issue. I tried both, but the cursor kept spinning. I may not have waited long enough for the results. So, I will try again. Thank you, again!
