Decompress Base64 code from Json File?

[-Enric-]

Good afternoon community,

I have a table used as Master data that follows the following format

2-digit	3-digit	Country	Market
BG	BGR	BULGARIA	EAST
CZ	CZE	CZECH REPUBLIC	EAST
HR	HRV	CROATIA	EAST

and I want to add a couple of additional rows, however when checking the source of the table in power query this is what I see;

=Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dVNbjusgDN1K1e/ZhBNcQCEQGejctpr9b+P6EU1Dqvk5Ac7x23m9rhNevxiSoY991VPG1P9df75e19T5QS6KuE6lkz9pspjn5ASxBaQE2dWTCBo/QDesjSLwycEclOXPF4NkU79je5qPo8IJ51DS8Ugr5MeRnTQnT4I9eTD3CLWZ+6e4f6LhHC6EW59SnI+iINaB7iKiAm10ESRy6NmQIzyO7Cq5rk4s1pJcuQ+2mzRoKztaYb8kFb5R6Ypc1xiWpI+VJkGkaSTrIs93xcQxlxMdlc47jSfXN6n3RmCYZ3wfdGCSpeXKWEiFcUJprfAomWHdxPsGMY+sl9g+ToYEqQGNik1WYSPFQq17SKMgChUbGKbH71drk0Wtq/iskC8rTzyXQeGkLS4rYmaBnHIhF+dqBaAW0AyLdecouEkNN62McZ/bUZDENjX9RWILHT59JFmnpOuQoN0/eE2aHwy/4XHiq/0SO3IhJ36RzV5gR1hCbSAa6rXufaRu9/frQHe594UMeZA48l5nOCmfY0N3WWL2rqxqYJPS0ZLOj3Bv1M71xc5/W2tmvcJbwjU0XS+utIULrLwTs2Uzi2zWEhnBwafq5z8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"2-digit" = _t, #"3-digit" = _t, Country = _t, Market = _t])

This is a inherited model, so I have no clue whatsoever on why this was done like this.

So my questions are;

1st What is happening here? a Jsone file is coded in base 64 to be decompressed and populate the table?

2nd what should be the rigth steps to follow to add further columns?

3rd Why do you think this was done like this instead of having the MD in a single excel file? was for performance reasons?

thank you for your help in advance,

regards,

[-Enric-]

Thank you! this was the solution!

[AlexisOlson · Accepted solution]

This is the standard way Power BI encodes data created using the Enter Data interface.

As mentioned in a previous thread, you can modify this data by clicking on the gear icon next to the first applied step:

FYI, I've written a custom function to convert this sort of table into a more human-readable one. Using this function on your table results in the following (after adding #" " around the first two column names):

let
  Source = Table.FromRows(
    {
      {"BE", "BEL", "BELGIUM", "BENELUX"},
      {"LU", "LUX", "LUXEMBOURG", "BENELUX"},
      {"NL", "NLD", "NETHERLANDS", "BENELUX"},
      {"AT", "AUT", "AUSTRIA", "DACH"},
      {"CH", "CHE", "SWITZERLAND", "DACH"},
      {"DE", "DEU", "GERMANY", "DACH"},
      {"BG", "BGR", "BULGARIA", "EAST"},
      {"CZ", "CZE", "CZECH REPUBLIC", "EAST"},
      {"HR", "HRV", "CROATIA", "EAST"},
      {"HU", "HUN", "HUNGARY", "EAST"},
      {"MD", "MDA", "MOLDOVA", "EAST"},
      {"PL", "POL", "POLAND", "EAST"},
      {"RO", "ROU", "ROMANIA", "EAST"},
      {"RS", "SRB", "SERBIA", "EAST"},
      {"SK", "SVK", "SLOVAKIA", "EAST"},
      {"SI", "SVN", "SLOVENIA", "EAST"},
      {"FR", "FRA", "FRANCE", "FRANCE"},
      {"AD", "AND", "ANDORRA", "IBERIA"},
      {"ES", "ESP", "SPAIN", "IBERIA"},
      {"GI", "GIB", "GIBRALTAR", "IBERIA"},
      {"PT", "PRT", "PORTUGAL", "IBERIA"},
      {"IT", "ITA", "ITALY", "ITALY"},
      {"SM", "SMR", "SAN MARINO", "ITALY"},
      {"DK", "DNK", "DENMARK", "NORDICS"},
      {"EE", "EST", "ESTONIA", "NORDICS"},
      {"FI", "FIN", "FINLAND", "NORDICS"},
      {"LT", "LTU", "LITHUANIA", "NORDICS"},
      {"LV", "LVA", "LATVIA", "NORDICS"},
      {"NO", "NOR", "NORWAY", "NORDICS"},
      {"SE", "SWE", "SWEDEN", "NORDICS"},
      {"KZ", "KAZ", "KAZAKHSTAN", "RUSSIA"},
      {"RU", "RUS", "RUSSIA", "RUSSIA"},
      {"UA", "UKR", "UKRAINE", "RUSSIA"},
      {"GB", "GBR", "UNITED KINGDOM", "UK"},
      {"IR", "IRL", "IRELAND", "UK"},
      {"UK", "UK", "UNITED KINGDOM", "UK"},
      {"US", "USA", "UNITED STATES", "NORTH AMERICA"},
      {"CA", "CAN", "CANADA", "NORTH AMERICA"}
    },
    type table [#"2-digit" = text, #"3-digit" = text, Country = text, Market = text]
  )
in
  Source