CODEWITHSUNDEEP
powerbidaxpowerquerym
slauer12
slauer12

Reputation: 55

Power Query Advanced Editor (Json.Document(Binary.Decompress(Binary.FromText)

I have seen older posts on here and other forums with the similar code to the one below and cannot figure out where a portion of it comes from. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlHSUXJ0BBJOiUXJqTn5eYlAdlhiTmpecmaiUqxOtJIpTAVcFFkxSIUZTIVvYkpRZgqQEZSfC5Eyh0mBReAqQFIW2GxGkrfEMBXVVkMDkAInfE43NIQpwel2QyOQEmesjjc0hslhut7QBCaHbDtCqymaViRbYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Company = _t, Origin = _t, Destiny = _t]),
    Group = Table.Group(Source, {"Origin"}, {{"Count", each List.Count(List.Distinct(_[Destiny])), type number}, {"Destinations", each Table.FromRows({List.Distinct(_[Destiny])}), type table}}),
    AllColumnNames = Table.ColumnNames(Table.Combine(Group[Destinations])),
    #"Expanded Destinations" = Table.ExpandTableColumn(Group, "Destinations", AllColumnNames, AllColumnNames)
in
    #"Expanded Destinations"

What I cannot figure out is where the "i45WMlHSUXJ0BBJOiUXJqTn5eYlAdlhiTmpecmaiUqxOtJIpTAVcFFkxSIUZTIVvYkpRZgqQEZSfC5Eyh0mBReAqQFIW2GxGkrfEMBXVVkMDkAInfE43NIQpwel2QyOQEmesjjc0hslhut7QBCaHbDtCqymaViRbYwE=" part comes from. I know it can be from if data is manually entered but I have also seen where it can link to other data sources. I have a use case where I have calculated columns in dax but need to manipulate those calculated columns in Power Query. Is there a place where this string is stored for each data source? I understand everything after the source line but this has me stumped.

Upvotes: 5

Views: 17126

Answers (2)

Scarlett
Scarlett

Reputation: 11

It's base 64 code. You can generate it using free online converter. So if you make a CSV file of the table you want, convert it to JSON format (using an online converter), then convert that to base 64 code (online converter again). Paste into the formula between the quote marks. Now you an generate static tables from any data source without having to type them all in to the back end.

Upvotes: 1

Alexis Olson
Alexis Olson

Reputation: 40204

When you input data manually using Enter Data from the Power BI Home tab, the query it generates looks like that first line. Basically, it takes the information you put in and converts it into a compressed form that isn't human-readable to save space. That first line is decompressing it back into a table format.

You should still be able to edit that table by clicking on the little gear icon next to the first applied step in the query editor. If you edit it, that string will change.

Upvotes: 9

Related Questions