Cells Offset

[Mic1979]

Dear all,

I need to shift the cells on the right in my workload.

The idea is this:

1. I need to check what stored is in the column "Name".

2. If in the cells of the column "Name" I have the word "Labour", then the value stored in the column "Part Number", needs to put in the column "Labour Cost (MATURE). Differently, no change

I thought to this code:

OffsetLabour = Table.FromRecords(
Table.TransformRows(#"Changed Type1",
(r)=> Record.TransformFields(
r, {{"Name", each if Text.Contains(_,"Labour") then r[#"Labour Cost (MATURE)"]=r[#"Part Number"] else null}})))

However, this is not working

Could you support?

Thanks

[Mic1979]

Thanks for your suggestions.

In the meatime, i made the following:

OffsetLabour = Table.FromRecords(
Table.TransformRows(#"Added Custom",
(r)=> Record.TransformFields(
r,
{
{"Labour Cost (MATURE)", each if Text.Contains(r[Name],"Labour") then r[#"Part Number"] else null},
{"Part Number", each if Text.Contains(r[Name],"Labour") then null else _}
}
)
)
),

[slorin]

Hi @Mic1979 

...
OffsetLabour1 = Table.ReplaceValue(#"Changed Type1", each Text.Contains([Name],"Labour"), each [#"Part Number"],
     (actual, test, new) => if test then new else actual, {"Labour Cost (MATURE)"}),
OffsetLabour2 = Table.ReplaceValue(OffsetLabour1, each Text.Contains([Name],"Labour"), null, 
     (actual, test, new) => if test then new else actual, {"Part Number"})
...

Stéphane

[AlienSx · Accepted solution]

    OffsetLabour = ((cols) => Table.SplitColumn(
        Table.CombineColumns(
            #"Changed Type1", 
            cols, 
            (x) => if Text.Contains(x{0}, "Labour") then {x{0}, x{2}, x{1}} else x,
            "combined"
        ),
        "combined", 
        (x) => x, 
        cols
    ))({"Name", "Part Number", "Labour Cost (MATURE)"})