Power Query
Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: Power Query
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2018
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Power Query

    I have a query that's connected to a file that's supposed to automatically every day. Today, for some reason (not my issue), the file wasn't there, but my query didn't tell me the file was missing. Can I flag the query so that if the file is missing it alerts me?

    Thank you!

    Judi

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,553
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Power Query

    use Show Queries or open PQ editor and you should see error
    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
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    Board Regular
    Join Date
    Mar 2018
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query

    Hmm...I have it attached to a button - the button is set to refresh the query. Is there another way, or am I stuck manually checking? I want to get an error message pop up, with a hard stop so I have to click ok. Can I do that?

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

    Default Re: Power Query

    I don't like vba

    but post M-code
    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
    impossible things we do on the spot. for miracles you need to wait for a while

  5. #5
    Board Regular
    Join Date
    Mar 2018
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query

    It's just a refresh to refresh the "Get and Transform" query:

    Code:
    Sub RefreshAll()
    
    
    ThisWorkbook.RefreshAll
    
    
    End Sub
    Last edited by Domroy; Jun 14th, 2019 at 10:57 AM.

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

    Cool Re: Power Query

    I asked about M-code, not vba

    PQ Editor
    Advanced Editor
    Copy code from there and paste here between [CODE]...[/CODE]
    Last edited by sandy666; Jun 14th, 2019 at 11:07 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
    impossible things we do on the spot. for miracles you need to wait for a while

  7. #7
    Board Regular
    Join Date
    Mar 2018
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query

    Code:
    let
        Source = Csv.Document(File.Contents("O:\Dashboards\Disability Mgmnt\Data\DM_raw.txt"),[Delimiter="|", Columns=29, Encoding=65001, QuoteStyle=QuoteStyle.None]),
        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"MCA", type text}, {"MCA_MISS", type text}, {"PROV_NAME", type text}, {"PROV_MISS", type text}, {"MRN", type number}, {"PATIENT", type text}, {"FIRSTVIS", type date}, {"LASTVIS", type date}, {"LASTVIS_TYPE", type text}, {"APPT_STAT", type text}, {"NEXT_APPT", type datetime}, {"ACCT_ID", type number}, {"DOI", type date}, {"EMPLOYER", type text}, {"PAYOR", type text}, {"MOD_", Int64.Type}, {"OFF_", Int64.Type}, {"SURGERYDT", type datetime}, {"SURGERYPROC", type text}, {"CLOSEDREASON", type text}, {"ARX_MMI", type date}, {"FACE_PS", type date}, {"DX_ARX", type text}, {"ICD10", type text}, {"CASE_REVIEW_DATE", type date}, {"CASE_FOLLOWUP_DATE", type date}, {"CLOSED_DATE", type date}, {"FUT_MED", type text}, {"LAST_OPN_DT", type datetime}})
    in
        #"Changed Type"

  8. #8
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,553
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Power Query

    oops, sorry wrong code
    Last edited by sandy666; Jun 14th, 2019 at 12:26 PM.
    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
    impossible things we do on the spot. for miracles you need to wait for a while

  9. #9
    Board Regular
    Join Date
    Mar 2018
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query

    LOL - no worries. Where can I find the code you need?

  10. #10
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,553
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Power Query

    I posted wrong code not you
    yours is ok.
    but is it possible to post link to shared your txt file? on OneDrive, GoogleDrive or any similar
    hard to work without source
    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
    impossible things we do on the spot. for miracles you need to wait for a while

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
  •