Forum Discussion

kracrde's avatar
kracrde
Frequent Visitor
7 years ago
Solved

Question on data formatting

Hi all,   I have a problem with data formatting I am not able to resolve myself. I hope someone can give me a good hint on how to solve it. I have a table with item numbers, description etc. The i...
Need Help
  • affan's avatar
    affan
    7 years ago

    Hi kracrde,

     

    You can model your data using the Query editor. As you have different cases in the data, some are having a delimiter '-' and some are starting with an alphabet. You need to apply the cleanup to all cases. Please see the video below that I created for you.

     

     

    If this helped you, please mark this post as an accepted solution and like to give KUDOS .

     

    Regards,

    Affan

kracrde's avatar
kracrde
Frequent Visitor
7 years ago

hi sreenathv

 

thank you for your quick reply. your formula has brought me a step further.

The numbers are correctly formatted except all which have an ending -0010 and higher.

All endings between -0001 and -0009 are fine.

 

e.g. 78644-0001 to 78644-0009 are ok

78644-0010 to 78644-9999 still show the -ending.

PattemManohar's avatar
PattemManohar
Community Champion
7 years ago

kracrde Please try this in "Power Query Editor" by adding a "Custom Column"

 

if Value.Is(Value.FromText(Text.End([Value],4)),type number) and Text.Start(Text.End([Value],5),1) = "-" then "78644" else [Value]