none
Token Comma Expected in Query to Import Data From Text Files RRS feed

  • Question

  • Hello Friends,

    the other day Colin and Imke helped me with a power query.

    The query is to import only selected text from each text file between START and END into power query.


    https://social.technet.microsoft.com/Forums/en-US/a9557aaf-0382-4d9f-848f-937b2fad88af/power-query-selected-import-from-text-files?forum=powerquery

    I have now added to the code to try and get all the files from the folder

    let
        Source = Lines.FromBinary(Folder.Files.Contents("C:\Users\Dan\Desktop\Files"),
        ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        TrimmedText = Table.TransformColumns(ConvertedToTable,{},Text.Trim),
        PositionOfStart = List.PositionOf(TrimmedText[Column1], "START"),
        RemovedRowsFromTop = Table.RemoveFirstN(TrimmedText, PositionOfStart + 1),
        PositionOfEnd = List.PositionOf(RemovedRowsFromTop[Column1], "END"),
        RemovedRowsFromBottom = Table.RemoveRows(RemovedRowsFromTop, PositionOfEnd, Table.RowCount(RemovedRowsFromTop) - PositionOfEnd)
    in
        RemovedRowsFromBottom

    I am afraid  something has gone wrong :(

    I couldn't find anything on google to help me solve this

    thank you for any advice


    Cheers Dan

    Saturday, July 16, 2016 2:27 PM

Answers

  • Hi Dan,

    there is no such function. You can find the M-function-library here: https://msdn.microsoft.com/en-us/library/mt253322.aspx?f=255&MSPPError=-2147217396 and some other learning resources here: http://www.thebiccountant.com/learning-resources/

    Your example needs to be adjusted like this:

    let 
    
    // Turn Colins query into a function so that it can be applied to every row of your table / file in your folder
    
    function = (Content) =>
    
    let
        Source = Lines.FromBinary(Content)),
        ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        TrimmedText = Table.TransformColumns(ConvertedToTable,{},Text.Trim),
        PositionOfStart = List.PositionOf(TrimmedText[Column1], "START"),
        RemovedRowsFromTop = Table.RemoveFirstN(TrimmedText, PositionOfStart + 1),
        PositionOfEnd = List.PositionOf(RemovedRowsFromTop[Column1], "END"),
        RemovedRowsFromBottom = Table.RemoveRows(RemovedRowsFromTop, PositionOfEnd, Table.RowCount(RemovedRowsFromTop) - PositionOfEnd)
    in
        RemovedRowsFromBottom,
    
    // Access the content of your folder 
    
       Source0 = Folder.Files("C:\Users\Dan\Desktop\Files"),
    
    // Pass the content of column "Content" to the function above
    
       #"Added Custom" = Table.AddColumn(Source0, "Custom", each function([Content]))
    in
        #"Added Custom"
    
    
    
    
    


    Imke Feldmann TheBIccountant.com


    Sunday, July 17, 2016 9:58 AM
    Moderator

All replies

  • Hi Dan,

    I know this error-message all too well - it normally comes with a syntax error - often missing a closing bracket. In the example you've given there's a closing bracket missing in the first step already.

    Please come back if this doesn't solve the problem.


    Imke Feldmann TheBIccountant.com

    Saturday, July 16, 2016 6:53 PM
    Moderator
  • Hello Imke,

    well spotted, I had to squint 5 times to finally see the error - I was missing a bracket.

    Sadly something else popped up now

    Expression Error : 'Folder.Files.Contents' wasn't recognized


    Cheers Dan

    Saturday, July 16, 2016 7:47 PM
  • Hi Dan,

    there is no such function. You can find the M-function-library here: https://msdn.microsoft.com/en-us/library/mt253322.aspx?f=255&MSPPError=-2147217396 and some other learning resources here: http://www.thebiccountant.com/learning-resources/

    Your example needs to be adjusted like this:

    let 
    
    // Turn Colins query into a function so that it can be applied to every row of your table / file in your folder
    
    function = (Content) =>
    
    let
        Source = Lines.FromBinary(Content)),
        ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        TrimmedText = Table.TransformColumns(ConvertedToTable,{},Text.Trim),
        PositionOfStart = List.PositionOf(TrimmedText[Column1], "START"),
        RemovedRowsFromTop = Table.RemoveFirstN(TrimmedText, PositionOfStart + 1),
        PositionOfEnd = List.PositionOf(RemovedRowsFromTop[Column1], "END"),
        RemovedRowsFromBottom = Table.RemoveRows(RemovedRowsFromTop, PositionOfEnd, Table.RowCount(RemovedRowsFromTop) - PositionOfEnd)
    in
        RemovedRowsFromBottom,
    
    // Access the content of your folder 
    
       Source0 = Folder.Files("C:\Users\Dan\Desktop\Files"),
    
    // Pass the content of column "Content" to the function above
    
       #"Added Custom" = Table.AddColumn(Source0, "Custom", each function([Content]))
    in
        #"Added Custom"
    
    
    
    
    


    Imke Feldmann TheBIccountant.com


    Sunday, July 17, 2016 9:58 AM
    Moderator
  • Thank you Imke,

    I am really loving power query -I find it nicer than excel vba - the interface is clean and some of the functions are easy to use.

    I have jumped into it without learning all  the syntax - I am watching all the videos on you tube and I came across some nice websites like excelguru.ca, that is very advanced.

    The above did the Job nicely :)

    Lastly I am trying to work out where to put your code 

    So that the Contents between START and END  are in 1 cell from each text file

     CombineRows = Table.AddColumn(SelectRange, "Result", each Text.Combine([Custom.2][Column1], "#(lf)"))
    in
        CombineRows

    I'm not sure do I add another Query or do I add in to existing code, I will investigate


    Cheers Dan

    Sunday, July 17, 2016 1:06 PM
  • Hi Dan,

    so we share the same view on vba - nice :-)

    This code goes at the end like this:

    let

    // Turn Colins query into a function so that it can be applied to every row of your table / file in your folder

    function = (Content) =>

    let
        Source
    = Lines.FromBinary(Content)),
        ConvertedToTable
    = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        TrimmedText
    = Table.TransformColumns(ConvertedToTable,{},Text.Trim),
        PositionOfStart
    = List.PositionOf(TrimmedText[Column1], "START"),
        RemovedRowsFromTop
    = Table.RemoveFirstN(TrimmedText, PositionOfStart + 1),
        PositionOfEnd
    = List.PositionOf(RemovedRowsFromTop[Column1], "END"),
        RemovedRowsFromBottom
    = Table.RemoveRows(RemovedRowsFromTop, PositionOfEnd, Table.RowCount(RemovedRowsFromTop) - PositionOfEnd)
    in
        RemovedRowsFromBottom
    ,

    // Access the content of your folder

       Source0
    = Folder.Files("C:\Users\Dan\Desktop\Files"),

    // Pass the content of column "Content" to the function above

      
    #"Added Custom" = Table.AddColumn(Source0, "Custom", each function([Content])),

    CombineRows = Table.AddColumn(  #"Added Custom", "Result",each Text.Combine([Custom][Column1], "#(lf)"))
    in
       
    CombineRows


    Imke Feldmann TheBIccountant.com


    Sunday, July 17, 2016 3:14 PM
    Moderator
  • Hi Imke,

    I couldn't work out what needs to be done here

    Expression error: The name 'SelectRange' wasn't recognized.  Make sure it's spelled correctly.

    I have been looking at this for text functions

    https://msdn.microsoft.com/en-us/library/mt296604.aspx

    but no SelectRange

    thank you for your help


    Cheers Dan

    Sunday, July 17, 2016 4:06 PM
  • Ooops - so sorry - my mistake: Have edited my post above. This is not a name of a function, but the name of the previous step.

    So the last steps are like this:

    #"Added Custom" = Table.AddColumn(Source0, "Custom", each function([Content])),

    CombineRows = Table.AddColumn(  #"Added Custom", "Result",each Text.Combine([Custom][Column1], "#(lf)"))
    in
       
    CombineRows


    Imke Feldmann TheBIccountant.com

    Sunday, July 17, 2016 4:31 PM
    Moderator
  • Thank you very much IMKE,

    for your generous help and step by step instruction.

    You are a superstar!

    I hope you will have a great sunday!

    The weather is beautiful here, I hope it is where you are

     - enjoy your Sunday!

              :)


    Cheers Dan

    Sunday, July 17, 2016 4:50 PM