Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
-Enric-
Regular Visitor

Decompress Base64 code from Json File?

Good afternoon community,

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

2-digit3-digitCountryMarket
BGBGRBULGARIAEAST
CZCZECZECH REPUBLICEAST
HRHRVCROATIAEAST

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,

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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:

AlexisOlson_0-1718038014477.png

 

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

 

View solution in original post

2 REPLIES 2
-Enric-
Regular Visitor

Thank you! this was the solution!

AlexisOlson
Super User
Super User

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:

AlexisOlson_0-1718038014477.png

 

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.