DEV Community

Dror Atariah
Dror Atariah

Posted on

Encoding an Excel File in a Power BI Report

Don't ask me why, but in my team we have a Power BI report that has an Excel file embedded in it. Kind of hard coded. Now, the time came and the Excel had to be updated. So, with the following snippet I created a new binary representation and replaced it in the right query:

let
    Source = Excel.Workbook(File.Contents("C:\Path\to\my\file.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Converted to List" = Table.ToRows(#"Promoted Headers"),
    #"Serialized to JSON" = Json.FromValue(#"Converted to List"),
    #"Compressed to Binary" = Binary.Compress(#"Serialized to JSON", Compression.Deflate),
    #"Converted to Text" = Binary.ToText(#"Compressed to Binary", BinaryEncoding.Base64)
in
    #"Converted to Text"
Enter fullscreen mode Exit fullscreen mode

In turn, I could copy the generated string and replace it in the line:

Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("binaryRepresentationOfTheExcel", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, Foo = _t, Bar = _t]),
Enter fullscreen mode Exit fullscreen mode

Top comments (0)