Power Query Column Transformation

%3CLINGO-SUB%20id%3D%22lingo-sub-1409526%22%20slang%3D%22en-US%22%3EPower%20Query%20Column%20Transformation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1409526%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20all.%20I%20am%20struggling%20to%20find%20a%20way%20to%20remove%20%2250%2F50%20Romaine%22%20from%20the%20%22Work%20Date%22%20column%20and%20move%20that%20to%20another%20column%20(that%20I%20will%20label%20%22Commodity%22).%20Also%20-%20this%20data%20has%20several%20blocks%20of%20data%20like%20that%20pictured%20but%20with%20different%20%22Commodity%22%20names.%20For%20example%2C%20the%20block%20of%20data%20below%20the%20screenshot%20has%20%22Broccoli%22%20on%20top%20of%20the%20%22Work%20Dates%22.%20So%20-%20Ill%20need%20a%20solution%20that%20will%20adress%20this%20issue%20all%20the%20way%20down%20the%20Work%20Date%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20any%20help%20provided!%20Paul%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(44).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F193631i04F8D04E7DA33C37%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Screenshot%20(44).png%22%20alt%3D%22Screenshot%20(44).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1409526%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1410001%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Column%20Transformation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1410001%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676026%22%20target%3D%22_blank%22%3E%40slohombre%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20such%20sample%20-%20duplicate%20the%20column%20and%20apply%20date%20format%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20365px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F193689i274535FEE7F6FED0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EWe%20have%20errors%20where%20are%20texts.%20Now%20let%20add%20Commodity%20column%20using%20formula%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%20%20%20%20%23%22Added%20Custom%22%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20%23%22Changed%20Type%22%2C%0A%20%20%20%20%20%20%20%20%22Commodity%22%2C%0A%20%20%20%20%20%20%20%20each%0A%20%20%20%20%20%20%20%20%20%20%20%20try%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20if%20%5B%23%22Work%20Date%20-%20Copy%22%5D%20%3D%20null%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20then%20%22!NULL!%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20else%20null%0A%20%20%20%20%20%20%20%20%20%20%20%20otherwise%20%5B%23%22Work%20Date%22%5D%0A%20%20%20%20)%2C%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ENow%20fill%20it%20down%2C%20replace%20back%20!NULL!%20on%20null%20and%20remove%20temporary%20column%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20373px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F193693iB45BFFCFBC348E7C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1410048%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Column%20Transformation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1410048%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%20Thank%20you!%20I%20duplicated%20the%20column%20and%20did%20get%20the%20errors%20where%20the%20text%20was%20formerly%20located.%20I%20am%20having%20trouble%20with%20the%20next%20step%2C%20I%20assume%20I%20use%20the%20%22Custom%20Column%22%26nbsp%3B%20button%20for%20the%20formula.%20Could%20I%20get%20more%20basic%20instruction%20from%20you%20on%20the%20exact%20formula%20to%20put%20into%20the%20formula%20bar%3F%20Thanks%20for%20the%20help.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(45).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F193694iECC4ADC00414BB45%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Screenshot%20(45).png%22%20alt%3D%22Screenshot%20(45).png%22%20%2F%3E%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1410269%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Column%20Transformation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1410269%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676026%22%20target%3D%22_blank%22%3E%40slohombre%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESorry%2C%20I%20copy%2Fpasted%20that%20from%20Advanced%20Editor%20since%20corrected%20formula%20here.%20If%20in%20Add%20Custom%20Column%20window%20it%20looks%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20578px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F193746i0054198FA3C2A9E1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EYou%20may%20not%20care%20about%20formatting%20here%20and%20type%20everything%20in%20one%20string.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi, all. I am struggling to find a way to remove "50/50 Romaine" from the "Work Date" column and move that to another column (that I will label "Commodity"). Also - this data has several blocks of data like that pictured but with different "Commodity" names. For example, the block of data below the screenshot has "Broccoli" on top of the "Work Dates". So - Ill need a solution that will adress this issue all the way down the Work Date column.

 

Thanks for any help provided! PaulScreenshot (44).png

3 Replies
Highlighted

@slohombre 

For such sample - duplicate the column and apply date format

image.png

We have errors where are texts. Now let add Commodity column using formula as

    #"Added Custom" = Table.AddColumn(
        #"Changed Type",
        "Commodity",
        each
            try
                if [#"Work Date - Copy"] = null
                then "!NULL!"
                else null
            otherwise [#"Work Date"]
    ),

Now fill it down, replace back !NULL! on null and remove temporary column

image.png

@Sergei Baklan  Thank you! I duplicated the column and did get the errors where the text was formerly located. I am having trouble with the next step, I assume I use the "Custom Column"  button for the formula. Could I get more basic instruction from you on the exact formula to put into the formula bar? Thanks for the help. 

 

Screenshot (45).png 

Highlighted

@slohombre 

Sorry, I copy/pasted that from Advanced Editor since corrected formula here. If in Add Custom Column window it looks like

image.png

You may not care about formatting here and type everything in one string.