Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 22

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

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

    Default Re: Power Query

    OH! No, unfortunately I can't post this data. Hmm...any other way you can help? What info do you need?

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

    Default Re: Power Query

    ok, it would be much easier with source but I can create dummy data for that. It will take more time only.
    or
    if it's possible maybe 3 rows from source of txt (headers and two data rows (you can change sensitive data to any other)
    Last edited by sandy666; Jun 14th, 2019 at 01:32 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
    I don't use vba in any form!

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

    Default Re: Power Query

    Well, if you have the time, cool. All this does, is "Get and Transform Data" in a query. It pulls a text file into a table. It looks in a folder for the file, and if the file is not there, I want an error message. What other data can I give you?

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

    Default Re: Power Query

    see 2nd part of post#12 (or even without data, only headers line)

    all except data I see from M-code
    Last edited by sandy666; Jun 14th, 2019 at 01:40 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
    I don't use vba in any form!

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

    Default Re: Power Query

    MCA
    MCA_MISS
    PROV_NAME
    PROV_MISS
    MRN
    PATIENT
    FIRSTVIS
    LASTVIS
    LASTVIS_TYPE
    APPT_STAT
    NEXT_APPT
    ACCT_ID
    DOI
    EMPLOYER
    PAYOR
    MOD_
    OFF_
    SURGERYDT
    SURGERYPROC
    CLOSEDREASON
    ARX_MMI
    FACE_PS
    DX_ARX
    ICD10
    CASE_REVIEW_DATE
    CASE_FOLLOWUP_DATE
    CLOSED_DATE
    FUT_MED
    LAST_OPN_DT


    Those are the headers.

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

    Cool Re: Power Query

    ok, try this on copy of your original file

    replace M-code in Advanced Editor with this below:

    Code:
    let
        TFR = Table.TransformColumnTypes(Table.FromRecords({[DateTime = DateTime.FixedLocalNow(),Type = "Error",Kind = "Source data is not available"]}),{{"DateTime", type datetime}}),
        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" = try 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}}) otherwise TFR
    in
        #"Changed Type"
    it will give you a small info table instead of table of your txt file.

    DateTime Type Kind
    14/06/2019 19:54
    Error Source data is not available


    if txt file will be available then you should see the data from there (after refresh of course )
    Everything after refresh
    Last edited by sandy666; Jun 14th, 2019 at 02:00 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
    I don't use vba in any form!

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

    Cool Re: Power Query

    one more thing

    honestly, you don't need vba button
    refresh time interval you can define here:



    or if you want suddenly refresh use Ctrl+Alt+F5 or Refresh All from the ribbon
    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!

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

    Default Re: Power Query

    COOL! So i can set it to refresh every morning?

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

    Default Re: Power Query

    Also, I'll let you know how the code is working once I can get back into the document and save a test file. Right now, it's crashing my work computer.

    But with the connection refresh, it'll do that even if the document is closed?

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

    Default Re: Power Query

    set Refresh data when opening the file
    and you'll get fresh data if you open the file in the morning or whenever you open it

    which document? txt or excel file?

    Quote Originally Posted by Domroy View Post
    Right now, it's crashing my work computer.
    I hope it's not my fault
    Last edited by sandy666; Jun 14th, 2019 at 02:59 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
    I don't use vba in any form!

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
  •