Power Query

[Domroy]

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

 

[Domroy]

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

 

[sandy666]

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)

 

[Domroy]

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?

 

[sandy666]

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

all except data I see from M-code

 

[Domroy]

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.

 

[sandy666]

ok, try this on copy of your original file

replace M-code in Advanced Editor with this below:

[SIZE=1]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"[/SIZE]

it will give you a small info table instead of table of your txt file.

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]DateTime[/td][td=bgcolor:#70AD47]Type[/td][td=bgcolor:#70AD47]Kind[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

14/06/2019 19:54​

[/td][td=bgcolor:#E2EFDA]Error[/td][td=bgcolor:#E2EFDA]Source data is not available[/td][/tr]
[/table]

if txt file will be available then you should see the data from there (after refresh of course )
Everything after refresh

 

[sandy666]

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

 

[Domroy]

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

 

[Domroy]

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?

 

[sandy666]

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?

Right now, it's crashing my work computer.

I hope it's not my fault