Aug 09 2021 10:48 AM
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] + 1} = [Order Group Primary Number] {[Index]} then null else [Original Contract])
I am getting the error below. I had changed the type of the [Order Group Primary Number] to text in a step above. Any assistance will be greatly appreciated with this leading space.
Expression.Error: We cannot convert the value " 134955" to type List.
Aug 09 2021 12:54 PM
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.
Aug 09 2021 12:57 PM
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]
)
Aug 09 2021 01:35 PM
@Sergei Baklan 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!
Aug 20 2021 02:53 PM
Aug 20 2021 09:46 PM
Aug 21 2021 02:47 AM
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.
Aug 23 2021 12:26 PM
Aug 23 2021 12:27 PM
Aug 23 2021 01:08 PM
Power Query is optimized to work with tables, not with lists and formulas in transformation. That's only to play with different variants.
You may try
- add Index to source
- reference this query, remove duplicates on "Order Group Primary Number" and Custom column with duplicating of "Original Contract"
- reference source with index again, here add Custom column with null in each record
- append latest two one to another,
sort ascending on Primary Number and descending on Index,
add another Index to fix table in memory,
remove duplicates on first Index,
remove unused columns
result is like this
Please check in attached file.
Aug 24 2021 05:38 AM
@Deirdre1720 The syntax to use try & otherwise in the example from @Sergei Baklan could look like this:
#"Added Custom" = Table.AddColumn(
#"Added Index",
"Custom",
each
if try #"Added Index"[Order Group Primary Number]{[Index] + 1} =
#"Added Index"[Order Group Primary Number]{[Index]}
then null
else [Original Contract] otherwise null
)
In this case if [Index]+1 is greater then max of field Index Power Query will throw an error and will got to the code after otherwise statement an execute it (set null as value).
I also had problems with long running code
#"Added Index" = Table.AddIndexColumn(#"Inserted Merged Column", "UID", 0, 1, Int64.Type),
Klucz = List.Buffer(#"Added Index"[Klucz]),
gr = List.Buffer(#"Added Index"[WallThickness]),
Custom1 = #"Added Index",
#"Added Custom" = Table.AddColumn(Custom1, "WallThicknessFrom", each try if Klucz{[UID]-1}=Klucz{[UID]} then gr{[UID]-1} else -1 otherwise -1),
In the code above I had to compare values from current record with value from previous. I created two lists (Klucz = List.Buffer(#"Added Index"[Klucz]), AND gr = List.Buffer(#"Added Index"[WallThickness]),)
with List.Buffer statement. This keeps list in memory and query works really fast. As you can see here I'm also using try and otherwise statement.
Regards,
Artur