Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
flinch
Helper I
Helper I

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 difference - I end up with many item codes that are not found, and when I look at the final query, it is obvious that it  isn't working because the one inventory code has these spaces after it and that is affecting the merge.

 

But other than going to my source file, and removing the spaces there, which for obvious reasons I want to avoid, how do I get rid of these things?

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

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

 

View solution in original post

17 REPLIES 17
Anonymous
Not applicable

it is not that, by chance, when you point to the tables to merge, you refere to the "wrong" steps: someone precedes the one in which you did cleaning?

 

could you show the content of the advanced editor of your queries?

Hi Rocco,

 

This is what I think is the issue as well. It seems to be merging the queries at a time when the cleaning and trimming doesn't appear to have happened yet. This is why I have ended up with clean and trim statements all over the show! I presume a query runs all the steps in the panel on the right, before it is then pulled across to merge with another query?

 

But I've got clean and trim statements everywhere now, and it still isn't coming right.

 

I will try post some more detail as per Jimmy's reply shortly.

Oh man, suddenly it's working. Cleaning and trimming in all the wrong places it would seem.

 

Thank you for the asistance all. I'm not sure exactly what the issue was, so hopefully it doesn't repeat!

Can you please mark one or more answers here as the solution so this thread can be shown to be solved @flinch and give Kudos (thumbs up) to all who helped?

 

I came in here to see if you had checked for ASCII char 160 which is a non-printing space used sometimes that can cause issues, but you seem to have your query working  now.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Jimmy801
Community Champion
Community Champion

Hello @flinch 

 

how are you applying the clean and trim-functions? Could you post your m-codes..

 

BR

 

Jimmy

Hi Jimmy,

 

I am just applying it using the menu options - selecting the column and then selceting trim and then clean. It is resulting in the following code...

 

= Table.TransformColumns(#"Filtered Rows",{{"fldInventoryCode", Text.Clean, type text}})

= Table.TransformColumns(#"Cleaned Text",{{"fldInventoryCode", Text.Trim, type text}})

 

Not sure if that helps?

Jimmy801
Community Champion
Community Champion

Hello @flinch 

 

and you are applying this to both of your tables? I see that you are referencing a filter-step before cleaning... this filter-step doesn't have any impact on the desired output, right?

 

BR

 

Jimmy

Hi Jimmy,

 

Capture.JPG

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!

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
Community Champion
Community Champion

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

 

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
Community Champion
Community Champion

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

 

Hi Jimmy,

 

Thanks for your help! I'm stuck in some meetings but when I get a free minute I will try and give a proper answer. It is tricky not being able to post the source files. Maybe I will try and reduce the info contained and post a summary.

It could be that your spaces are some obtuse character that is not technically a space but actually looks like a space. You could try clicking on one of the cells, copy one of the spaces (you can do that down the bottom) and then try "replace values" by pasting this character from the clipboard into the "find" box. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Hi Matt, thanks for the resposnse.

 

That's definitly happening in some places. I have "solved" half my problem by putting clean and trim functions after every step in the query, which while odd, seems to make a difference in some of them.

 

I will give your suggestion a bash and report back.

Matt, that has helped in some cases. Now I have items that appear to be identical. I can freely find them in either sheet, and they are just the code, i.e., when I copy/paste into find, they appear as I would expect them to. Something copied in one spreadsheet is directly found in the other spreadsheet with no issues. It just does not see the cost to merge!

 

It seems that although the output tables have identical codes, clearly at the point of the merge there is a difference. So I am cleaning and trimming in the wrong place. But there are only so many places to perform the function, so I just don't know what to do at this point!

Clearly something is wrong, but it's hard to know what. I would pick one code causing problems. Filter both tables before the merge for just that one code. That way you can better identify what is going on. Repeat for a few codes if needed. Hopefully you will discover something. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors