Results 1 to 10 of 10

Thread: Reversing a String of numbers or text with delimiters
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2016
    Location
    Perth WA
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Reversing a String of numbers or text with delimiters

    Hi all, new to power Query and I need a solution to the above problem. basically I have a list of prices in 1 cell that are separated by a full stop "."
    E.g. 10990. 10450. 10190. 9990
    And so on up to a max of 19 changes.
    I want to reverse these to show the below
    9990. 10190. 10450. 10990
    The reason for this is I want the last price change to show first for all rows so they align. Splitting the cell is the easy part so just need someone to help with the hard part..

  2. #2
    New Member
    Join Date
    Nov 2016
    Location
    Perth WA
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reversing a String of numbers or text with delimiters

    sorry should also mention that each row has a different amount of changes so wanting the last Price change to align example of saw data below

    10990. 9990
    8990. 7990. 8490. 7490
    12990

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,395
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Reversing a String of numbers or text with delimiters

    Is there a particular reason you need to use Power Query for this?
    What version of Excel are you using?
    What would be the approximate maximum number of such prices in a single cell?
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    3,027
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Reversing a String of numbers or text with delimiters

    is that what you want?

    raw Result
    10990. 9990 9990. 10990
    8990. 7990. 8490. 7490 7490. 8490. 7990. 8990
    12990 12990


    btw. delimiter in your example is ". " not only "."
    Last edited by sandy666; Sep 23rd, 2019 at 08:11 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  5. #5
    New Member
    Join Date
    Nov 2016
    Location
    Perth WA
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reversing a String of numbers or text with delimiters

    yes this is the result im looking for.

  6. #6
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    3,027
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Reversing a String of numbers or text with delimiters

    this is not copy/paste solution but you can try

    Code:
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Type = Table.TransformColumnTypes(Source,{{"raw", type text}}),
        Split = Table.SplitColumn(Type, "raw", Splitter.SplitTextByDelimiter(". ", QuoteStyle.Csv), {"raw.1", "raw.2", "raw.3", "raw.4"}),
        Demote = Table.DemoteHeaders(Split),
        Transpose = Table.Transpose(Demote),
        Sort = Table.Sort(Transpose,{{"Column1", Order.Descending}}),
        Transpose1 = Table.Transpose(Sort),
        Promote = Table.PromoteHeaders(Transpose1, [PromoteAllScalars=true]),
        Merge = Table.CombineColumns(Table.TransformColumnTypes(Promote, {{"raw.4", type text}, {"raw.3", type text}, {"raw.2", type text}, {"raw.1", type text}}, "en-GB"),{"raw.4", "raw.3", "raw.2", "raw.1"},Combiner.CombineTextByDelimiter(". ", QuoteStyle.None),"Result"),
        Replace = Table.ReplaceValue(Merge,".","",Replacer.ReplaceText,{"Result"}),
        Trim = Table.TransformColumns(Replace,{{"Result", Text.Trim, type text}}),
        Replace1 = Table.ReplaceValue(Trim," ",". ",Replacer.ReplaceText,{"Result"})
    in
        Replace1
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,395
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Reversing a String of numbers or text with delimiters

    Quote Originally Posted by Phill032 View Post
    yes this is the result im looking for.
    Are there answers to my questions too?
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #8
    New Member
    Join Date
    Nov 2016
    Location
    Perth WA
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reversing a String of numbers or text with delimiters

    Sorry, my laptop wont let me reply... such is my life today.. I run reports for 12 dealerships. Just switched to excel 2013 from 2007.
    The reason for power query is I can run many different queries from the 1 set of data. And also stack "apend" the data. All my previous reports have got bogged down with formulas and macros etc.
    There would be a max of 19 price changes within 1 cell.

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,395
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Reversing a String of numbers or text with delimiters

    Quote Originally Posted by Phill032 View Post
    The reason for power query is I can run many different queries from the 1 set of data. And also stack "apend" the data. All my previous reports have got bogged down with formulas and macros etc.
    There would be a max of 19 price changes within 1 cell.
    OK, thanks. I'll leave it to sandy666 &/or others then.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  10. #10
    Board Regular
    Join Date
    Nov 2017
    Posts
    80
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reversing a String of numbers or text with delimiters

    Code:
    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    MySort = (x) => Text.Combine(List.Reverse(Text.Split(x, ".")), ". "),
    Reversed= Table.AddColumn(Source, "Reversed", each MySort([raw]))
    in Reversed
    Last edited by horseyride; Sep 25th, 2019 at 03:56 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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