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"
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]),
Top comments (0)