CODEWITHSUNDEEP
powerbipowerquery
RCarmody
RCarmody

Reputation: 720

Automating An .XLS Report to Power BI

I am trying to utilize a Power BI dataset with Microsoft Flow for a daily update. Here's my current situation:

  1. Reports are exported every morning in .xls format onto SharePoint
  2. I open an Excel to refresh a Power Query that pulls the data from the report into a format for the dashboard as well as MS Flow (I tried scheduling a refresh directly to Power BI but it seems that I can't schedule a refresh to .xls)
  3. MS Flow & Power BI refresh off of the .xlsx file containing the queried data

Is there a way to avoid the manual step of opening up the Excel and hitting refresh on the Power Query?

let
    Source = SharePoint.Files("https://xxxxxxxxxx", [ApiVersion = 15]),
    FileContent = Source{[Name = "Individual Status.xls"]}[Content],
    ParsedTable = ParseXls(FileContent, "Individual Status")
in
    Source

Expression.Error: We cannot convert the value "Individual Status" to type Binary.
Details:
Value=Individual Status.xls
Type=[Type]

Upvotes: 0

Views: 930

Answers (1)

Kosuke Sakai
Kosuke Sakai

Reputation: 2411

A possible work around is to parse .xls file using Python or R script directly in Power BI.

Here is a sample function to parse .xls using Python. Copy and paste below code in a Blank Query, and rename it to "ParseXls" or some meaningful name.

(workbook as binary, sheet as text, optional header as logical) as table =>
    let dataset = Table.FromRecords({[
        content = Binary.ToText(workbook, BinaryEncoding.Base64),
        sheet = sheet,
        header = if header = null then true else header
    ]}),
    result = Python.Execute("import pandas as pd#(lf)from base64 import b64decode#(lf)from io import BytesIO#(lf)params = dataset.loc[0]#(lf)content = BytesIO(b64decode(params['content']))#(lf)sheet = params['sheet']#(lf)header = 0 if params['header'] else None#(lf)result = pd.read_excel(content, sheet, header=header)", [dataset = dataset]){[Name = "result"]}[Value]
    in result

Then you can use this function with binary file content you get from SharePoint. The entire query will be looking like this.

let
    Source = SharePoint.Files("https://yourcompany.sharepoint.com/...", [ApiVersion = 15]),
    FileContent = Source{[Name = "Book1.xls"]}[Content],
    ParsedTable = ParseXls(FileContent, "Sheet1"),
    ...

Upvotes: 1

Related Questions