+ Reply to Thread
Results 1 to 3 of 3

Power Query; help with left function

  1. #1
    Forum Contributor
    Join Date
    08-26-2009
    Location
    Iceland
    MS-Off Ver
    Excel 365, Windows 10
    Posts
    110

    Power Query; help with left function

    Hi last years I have use powerpivot.
    Now I want to improve my skills and need some help.

    Short version:
    But my problem with query is,
    I want to add some columns in my query.
    I have use Left function but I cannot find out how I solve it in query



    Long version:
    I am working with accounting data from year 2018,2019 and 2020.
    Until now I have added the data to one file.
    Until now I have also made a small addition, I use left function for a few additional rows.

    Now I want to use Power Query and combine my datafiles in some folder.
    I can do that.

    But my problem with query is,
    I want to add some columns in my query.
    I have use Left function but I cannot find out how I solve it in query


    Why do I use left function?
    I have accounting key which I want to split, I want to sort our by first number of the key, the first two etc.
    The reason for that, if I group my accounting key, I get better informations.

    In my file now I use: =left(accountingkey,1) etc


    If accounting key is 1234, I want a coulmn with 1 and another with 12
    If accounting key is 5432, I want a coulmn with 5 and another with 54
    etc.

    Thanks,
    Last edited by Reykjavik; 03-29-2020 at 11:04 AM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,872

    Re: Power Query; help with left function

    Possible Solution #1
    Why not use the split function and split based upon length. Then merge columns as needed in new column and delete uneeded columns.

    Possible Solution #2.
    The correct syntax for =Left(Text,num_char) is
    =Text.Start(text,num_chars)
    In your case =Text.Start([ColumnName], 1) in one column and
    =Text.Start([ColumnName],2) in the second column.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor
    Join Date
    08-26-2009
    Location
    Iceland
    MS-Off Ver
    Excel 365, Windows 10
    Posts
    110

    Re: Power Query; help with left function

    Thanks Alan,

    I tried solution 2,
    and got error

    (column with 63120, is a number cell)

    Expression.Error: We cannot convert the value 63120 to type Text.
    Details:
    Value=63120
    Type=Type

    I tried also other columns, always same problem, error.

    Sometimes I make stupid mistake.
    Finally I choose first the column (click on it) before I added a new column. This Text.Start formula works.

    Alan, thank you very much.
    It is solved

    Regards,
    Petur

+ 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. Power Query with Left Outer Merged problem
    By Eric Tsang in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 03-22-2020, 07:08 AM
  2. [SOLVED] Power Query - excel formula translation into Power Query
    By afgi in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 02-19-2020, 03:38 AM
  3. Replies: 4
    Last Post: 02-17-2020, 06:03 AM
  4. [SOLVED] Power Query NETWORKDAYS function
    By kersplash in forum Excel Formulas & Functions
    Replies: 27
    Last Post: 12-13-2019, 06:42 PM
  5. [SOLVED] Power Query - Converting a query into a function
    By kersplash in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-29-2019, 12:17 AM
  6. [SOLVED] Countifs function in Power Query?
    By slc in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-23-2019, 08:20 AM
  7. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM

Tags for this Thread

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