Forum Discussion

flinch's avatar
flinch
Helper I
5 years ago
Solved

Clean and Trim

Hi, I am trying to merge 2 inventory files using the item code. One of the queries has spaces after the item code, so I have both cleaned and trimmed the column. But it doesn't seem to make any diffe...
Need Help
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    Hello flinch 

     

    I've tried some experiments with your data, but no way that I was able to no join them. Here the code if somebody want to try.

    And so you are saying that exactly this 2 rows in your final table didn't join?

    let
    Table1 = 


    let
        Source = #table
            (
                type table [Column1 = number, Column2 = text, Column3 = text, Column4 = text, Column5 = text, Column6 = text, Column7 = text, Column8 = text],
                {
                    {"1105","BP01","BALL POINT PEN (EACH)","C","STATIONERY LOCAL","","0","No"}
                }
            ),
        Transform = Table.TransformColumns
        (
            Source,
            {
                {
                    "Column2",
                    each _,
                    type number
                }
            }
        )
    in
	    Transform,
        

Table2 =
    let
        Source = #table
        (
            type table [Column1 = datetime, Column2 = datetime],
            {
                {"BP01","1.095.944.348"}
            }
        )
    in
        Source,


Join= Table.NestedJoin
(
    Table1,
    "Column2",
    Table2,
    "Column1",
    "tbl2"
)
in
    Join


    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

     

flinch's avatar
flinch
Helper I
5 years ago

Hi Jimmy,

 

Here is a snapshot of the steps, if that helps. As you can see I am trimming and cleaning all over the show to try and fix it. I have done the same in the other query as well. The preceding step - the replace value - is to get rid of this horrible _x000D_ that appears all over my queries all the time. That's a separate issue altogether that I wouldn't mind solving as well!

 

I have tried removing all steps after the cleaning and trimming, as per Matt's suggestion, and then VLOOKUPing the resultant spreadsheets. The item codes seem to be correctly displaying in both queries at this point, and it works. But when I merge, these codes just don't get picked up.

I think I will try and rebuild it from scratch in the hope that something foolish has been done, and I just haven't seen it, unlss you have some other helpful suggestion?

 

Appreciate the assisstance!

flinch's avatar
flinch
Helper I
5 years ago

Sorry, I see the preceding step is actually filter rows - I am just removinginactive stock codes here, and the codes I am cncerned about are definitely active, so that isn't affecting the process that I can see.

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    Hello flinch 

     

    could you please send one row of each dataset that should join but they do not. Just select this single row in the last step and copy paste the raw data and tell the column index where you join.

     

    BR

     

    Jimmy

     

    • flinch's avatar
      flinch
      Helper I
      5 years ago

      fldInventoryID fldInventoryCode fldDescription fldPartType fldCategoryDesc fldModelNo fldManufacturerID fldSoleSupplier

      1105BP01BALL POINT PEN (EACH)CSTATIONERY LOCAL 0No

       

      This is what I get when I Ctrl C, Ctrl V. I have inserted spaces in the headings so you are able to make sense of them... The fldInventoryCode in this case is BP01, from my Inventory Master file.

       

      Code ZWLCost

      BP0110.95944348

       

      This is the Costs file, which has the Code and Cost.

       

      I get the feeling this isn't quite what you were after though?

      • Jimmy801's avatar
        Jimmy801
        Community Champion
        5 years ago

        Hello flinch 

         

        I've tried some experiments with your data, but no way that I was able to no join them. Here the code if somebody want to try.

        And so you are saying that exactly this 2 rows in your final table didn't join?

        let
    Table1 = 


    let
        Source = #table
            (
                type table [Column1 = number, Column2 = text, Column3 = text, Column4 = text, Column5 = text, Column6 = text, Column7 = text, Column8 = text],
                {
                    {"1105","BP01","BALL POINT PEN (EACH)","C","STATIONERY LOCAL","","0","No"}
                }
            ),
        Transform = Table.TransformColumns
        (
            Source,
            {
                {
                    "Column2",
                    each _,
                    type number
                }
            }
        )
    in
	    Transform,
        

Table2 =
    let
        Source = #table
        (
            type table [Column1 = datetime, Column2 = datetime],
            {
                {"BP01","1.095.944.348"}
            }
        )
    in
        Source,


Join= Table.NestedJoin
(
    Table1,
    "Column2",
    Table2,
    "Column1",
    "tbl2"
)
in
    Join


        If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
        Kudoes are nice too

        Have fun

        Jimmy