Forum Discussion

Mic1979's avatar
Mic1979
Post Partisan
1 year ago
Solved

Cells Offset

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

Need Help
  • AlienSx's avatar
    AlienSx
    1 year ago
        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)"})

3 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    1 year ago
        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)"})
  • slorin's avatar
    slorin
    Super User
    1 year ago

    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

  • Mic1979's avatar
    Mic1979
    Post Partisan
    1 year ago

    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 _}
    }
    )
    )
    ),