Hourly Data Source

[Anonymous]

Hello there,

I have a web data source below. It's hourly each day from 9am-4pm, one report each hour and repeats everyday. I would like to schedule my PBI 8 times a day. i scheduled them each hour from 9am-4pm so that PBI will choose that new hourly file once its available

https://companysales.com:timeReportHourly09_89294127_20200501.xlsx
https://companysales.com:timeReportHourly10_89607496_20200501.xlsx
https://companysales.com:timeReportHourly11_89562747_20200501.xlsx
https://companysales.com:timeReportHourly12_89628747_20200501.xlsx
https://companysales.com:timeReportHourly13_89603979_20200501.xlsx
https://companysales.com:timeReportHourly14_89897463_20200501.xlsx
https://companysales.com:timeReportHourly15_89609762_20200501.xlsx
https://companysales.com:timeReportHourly16_89452536_20200501.xlsx

https://companysales.com:timeReportHourly09_89294127_20200502.xlsx
https://companysales.com:timeReportHourly10_89607496_20200502.xlsx
https://companysales.com:timeReportHourly11_89562747_20200502.xlsx
https://companysales.com:timeReportHourly12_89628747_20200502.xlsx
https://companysales.com:timeReportHourly13_89603979_20200502.xlsx
https://companysales.com:timeReportHourly14_89897463_20200502.xlsx
https://companysales.com:timeReportHourly15_89609762_20200502.xlsx
https://companysales.com:timeReportHourly16_89452536_20200502.xlsx

My query below did not work, can someone help?

let
Source =
if      Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly16_89452536_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true) = null then Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly15_89609762_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true)
else 
if      Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly15_89609762_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true) = null then Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly14_89897463_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true)
else 
if      Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly14_89897463_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true) = null then Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly13_89603979_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true)
else 
if      Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly13_89603979_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true) = null then Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly12_89628747_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true)
else 
if      Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly12_89628747_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true) = null then Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly11_89562747_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true)
else 
if      Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly11_89562747_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true) = null then Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly10_89607496_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true)
else 
if      Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly10_89607496_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true) = null then Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly09_89294127_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true)
else    Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly16_89452536_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true)

in
Source

[Anonymous]

Appreciate for any help, can someone help to optimize my query? Multiple/nested ifs then else...logic -> If 4pm file is not available, use 3pm. If 3pm is not available use 2pm keep going till 9am

let
Source =
if      Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly16_89452536_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true) = null then Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly15_89609762_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true)
else 
if      Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly15_89609762_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true) = null then Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly14_89897463_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true)
else 
if      Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly14_89897463_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true) = null then Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly13_89603979_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true)
else 
if      Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly13_89603979_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true) = null then Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly12_89628747_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true)
else 
if      Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly12_89628747_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true) = null then Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly11_89562747_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true)
else 
if      Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly11_89562747_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true) = null then Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly10_89607496_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true)
else 
if      Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly10_89607496_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true) = null then Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly09_89294127_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true)
else    Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly16_89452536_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true)

in
Source

[mahoneypat]

You can do this by extract the date and hour from the file name and sorting on that, and then keeping the top row (or doing a relative reference to the top row with {0}.  See this query as an example with the filenames you provided.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdJNCoMwEIbhq5Ssi04mf44n6LpbEZEitKBVTAS9fVOXjRuTLrN5+Ia8VcWezk22zPPHOEzte7Nt39nMP0r3Grp7N42zu43L3G9ATUFIkqNpEBBAAc/W3q6svp5gOHhGg5GkkxjuGaXRyLQ1+F2DRSoj9qMEGUpipGcKMlKLJEbta8hoTGK0Z6RCJY5+6px1EA/+J54oJownignjiWLCeKKYMJ4oJownignjwZ94LqyuPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FileName = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"FileName", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([FileName] <> "" and [FileName] <> " ")),
#"Added Custom Column" = Table.AddColumn(#"Filtered Rows", "DateHour", each Text.Combine({Text.Middle([FileName], 53, 8), Text.Middle([FileName], 41, 2)}), type text),
#"Sorted Rows" = Table.Sort(#"Added Custom Column",{{"DateHour", Order.Descending}}),
#"Kept First Rows" = Table.FirstN(#"Sorted Rows",1)
in
#"Kept First Rows"

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Can you show me how to get below binary? The filenames I used are examples

Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdJNCoMwEIbhq5Ssi04mf44n6LpbEZEitKBVTAS9fVOXjRuTLrN5+Ia8VcWezk22zPPHOEzte7Nt39nMP0r3Grp7N42zu43L3G9ATUFIkqNpEBBAAc/W3q6svp5gOHhGg5GkkxjuGaXRyLQ1+F2DRSoj9qMEGUpipGcKMlKLJEbta8hoTGK0Z6RCJY5+6px1EA/+J54oJownignjiWLCeKKYMJ4oJownignjwZ94LqyuPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FileName = _t]),

[mahoneypat]

This is the code to add to the custom column.  I just used Add Column from examples, and typed the desired output in a few columns and it auto generated it.

Text.Combine({Text.Middle([FileName], 53, 8), Text.Middle([FileName], 41, 2)})

See this link for more info on column from examples.  A great feature.

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-add-column-from-example

Once you have it down to the one latest file (after you sort descending on the new column), you can get the file contents with a relative reference to the first row of that step.  The column with the file content is probably called [Content] if I remember correctly, so you can insert a new step and use =#"Sorted Rows"{0}[Content] on that step.  This assumes your previous step is #"Sorted Rows" (and the file content column is name [Content].

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Sorry if i was not clear, I do know how to use add custom column from example

I meant how did you convert a web link into rdJNCoMwEIbhq5Ssi04mf44n6LpbEZEitKBVTAS9fVOXjRuTLrN5+Ia8VcWezk22zPPHOEzte7Nt39nMP0r3Grp7N42zu43L3G9ATUFIkqNpEBBAAc/W3q6svp5gOHhGg5GkkxjuGaXRyLQ1+F2DRSoj9qMEGUpipGcKMlKLJEbta8hoTGK0Z6RCJY5+6px1EA/+J54oJownignjiWLCeKKYMJ4oJownignjwZ94LqyuPw==