+ Reply to Thread
Results 1 to 3 of 3

m code to Split decimal number from text in power query

  1. #1
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    EXCEL 2016
    Posts
    1,054

    m code to Split decimal number from text in power query

    I have a huge several csv file of 20, 000 records each which I combine in power query , the csv has many headers however my header MTT_MCH has values with decimal points, but csv file itself digits appears text 1008 and should be reading 10.08.

    All digits have two decimal places .

    How to split the decimal number from text . from 1008 to 10.08 e.g 450 to 4.50 .

    Is there a special M CODE that can do this in power query
    Attached Files Attached Files

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    7,219

    Re: m code to Split decimal number from text in power query

    Just divide it by 100?
    Rory

  3. #3
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,994

    Re: m code to Split decimal number from text in power query

    in the table the values are all numbers so table1[MTT_MCH]/100 would work

    if they are truly text
    =LEFT(Table1[MTT_MCH],len(Table1[MTT_MCH])-2)+right(Table1[MTT_MCH],2)/100

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Help with Power Query to split multiple values in a cell split by comma to multiple rows
    By chandramouliarun in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-05-2021, 05:50 AM
  2. [SOLVED] Help with Power Query to Split JSON into Columns
    By chandramouliarun in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-07-2020, 09:44 AM
  3. [SOLVED] Converting text to number in power query
    By JohannesB in forum Excel General
    Replies: 4
    Last Post: 04-02-2020, 11:01 AM
  4. Replies: 4
    Last Post: 02-17-2020, 06:03 AM
  5. [SOLVED] Power Query to Split a Column
    By andrewc in forum Excel General
    Replies: 5
    Last Post: 07-27-2019, 01:00 PM
  6. Power query and decimal separator
    By mikla90 in forum Excel General
    Replies: 4
    Last Post: 07-11-2019, 01:47 PM
  7. [SOLVED] power query error: we cannot apply operator - to types text and number
    By loganpmgoa in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-14-2019, 01:44 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1