Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Hourly Data Source

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

 

5 REPLIES 5
Anonymous
Not applicable

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
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

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]),

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

 

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

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==

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.