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

Question

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.

Alexis Olson Alexis Olson · Accepted Answer · 2020-02-13T18:50:47

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.

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

1 Answers