Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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==