Accessing SQL Blobs

[Anonymous]

Hi:

An ERP application stores document attachments in SQL as Blobs.

Is it possible for Power BI to access attachments for those who want to open, say, PDFs in a Power BI dashboard or report? If so, how?

Thank you!

John

[lbendlin · Accepted solution]

Yes, Power Query has a "Binary" data type that you can then bend to your will, for example by declaring that you want to read that binary as a PDF.

Just add the blob to your SQL view columns.

Here's an example where I pull a bunch of CSV files out of their blobs (the [Content] column)  

 #"Invoke Custom Function1" = Table.AddColumn(#"Renamed Columns1", "CSV", each #"Get CSV file"([Content])), 

via a custom function where I do some basic transforms:

Get CSV File = 

(File) => let
Source = Csv.Document(File,[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

....

[viktorglez]

There is another alternative that may interest you to solve the problem,

Indirectly you can create an extractor externally (by programming it) to read the blob and build the file temporarily then you could reach that file through a link for that record:

You have this:

Then:

[v-xicai]

Hi @Anonymous ,

You may refer to the article: How to access data from Azure Blob Storage using Power BI.

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks, Amy!

Would the same thing work for PDFs, as well as CSVs?

John

[lbendlin · Accepted solution]

Yes, Power Query has a "Binary" data type that you can then bend to your will, for example by declaring that you want to read that binary as a PDF.

Just add the blob to your SQL view columns.

Here's an example where I pull a bunch of CSV files out of their blobs (the [Content] column)  

 #"Invoke Custom Function1" = Table.AddColumn(#"Renamed Columns1", "CSV", each #"Get CSV file"([Content])), 

via a custom function where I do some basic transforms:

Get CSV File = 

(File) => let
Source = Csv.Document(File,[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

....

[Anonymous]

Frankly, I don't understand any of what you said.  Can you please supply some simple steps?  Does this sort of thing require prorgramming?  Can you not view the PDF attachments in Power BI?

[lbendlin]

Power BI is a Data Analytics tool, not a picture viewer.  There is a PDF visual somewhere but I think it is broken/unsupported.

What Power BI (Power Query) offers is a way to extract data from PDF files, like table data etc.

[Anonymous]

You told me, originally, that it could be done.  I told my boss that it could be done afterward, we relied on that information, and now I may have to cancel an important demo of this scheduled with a client for tomorrow.

[lbendlin]

Extracting data from PDF files that are stored as blobs - that can be done, and that is what I was referring to.  You did not mention that you want to display the PDF files in Power BI.

[Anonymous · Accepted solution]

Thank you.  I apologize, for getting upset.

How can that, at least, be done?  I cannot find any good and clear instructions on doing so.

[lbendlin]

Here is an example with XML, the process is similar with PDF

let
    Source = Sql.Database("server", "database"),
    Reports = Source{[Schema="schema",Item="table"]}[Data],
    #"1" = Reports{[id_report=1]},
    Meta = #"1"[Meta],
    #"Parsed XML" = Xml.Tables(Meta),

This connects to a SQL server database, fetches the first row of a table, then fetches the blob from the [Meta] column and then parses the data in the blob as XML.

From there on you can traverse the XML structure and extract the data, much in the same way as you would open the PDF blob and then extract the data from there. The only difference will be that you use Pdf.Tables from binary.

[Anonymous]

Again, I don;t understand any of that.  Woudl you please supply instructions?

[Anonymous]

Thank you, for all of your help, lbendlin!  The demo went fine!  You're great, and I really appreciate all that you do!

John

[Anonymous]

Hi:

You mentioned "Power Query".  What about Power BI?

John