CODEWITHSUNDEEP
excelpowerqueryurlfetch
xyz333
xyz333

Reputation: 768

Parse JSON with Power Query in Excel in the same way FetchJson does in Google sheets

I want to pare JSONs in Excel using Power Query that have the following structure

enter image description here

Here the link: https://sum-app.net/projects/15222520210510875/download_data/kumu_json

I can do this in Google sheets with FetchJson with the following but I have had no luck using Ecexls Power query doing the same

How to do this with Power Query in Excel?

Thanks in advance for your assistance with this

//get JSON Data for elements var data=FetchJSON(jsonlink ,"|", "elements");

//get Json Data for Connections var data=FetchJSON(jsonlink ,"|", "connections");

 //get Json Data for element
function FetchJSON(jsonlink ,separator, what) {

// Set Fetch Variables
  var response     = UrlFetchApp.fetch(jsonlink);
  var responseText = response.getContentText();
  var responseJson = JSON.parse(responseText);

  if(what=="elements"){
  let myDico = new Map();
  responseJson.elements.forEach(function(key){
    Object.keys(key).forEach(function(item){
       myDico.set(item,'')
    })
  })
  var elementKeys = []
  myDico.forEach(function(value, key) {
    elementKeys.push(key)
  });
  var data = responseJson.elements.map(e => elementKeys.map(f => {
      return e[f] instanceof Array ? e[f].join(separator) : e[f];
  })); 
  data.unshift(elementKeys);
  
  } 
  else if(what=="connections"){
   let myDico2 = new Map()
   responseJson.connections.forEach(function(key){
    Object.keys(key).forEach(function(item){
       myDico2.set(item,'')
    })
  })
  var connectionKeys = []
  myDico2.forEach(function(value, key) {
    connectionKeys.push(key)
  })
  var data = responseJson.connections.map(e => connectionKeys.map(f => {
      return e[f] instanceof Array ? e[f].join(separator) : e[f];
  })); 
  data.unshift(connectionKeys);

  };
  //data = data.map(x => x.map(y => typeof y === 'undefined' || null ? "" : y));

  return data
}

Edit: Here is what the output needs to look like (Column order in not important) Element data enter image description here

Connection data enter image description here

Google sheet with data https://docs.google.com/spreadsheets/d/1yO3gX61MroPrYL2l1YnfDxloa5naAX6HG2Q2f5ED_lY/edit?usp=sharing

Upvotes: 1

Views: 148

Answers (1)

horseyride
horseyride

Reputation: 21343

Try

let  Source =Json.Document(File.Contents("C:\Temp\kumu_json.json")),
Expanded=Table.FromRecords (Source[elements] ),
#"Added Index" = Table.AddIndexColumn(Expanded, "Index", 0, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
#"Combine items in list" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Value", each try if Value.Is(_, type list ) then Text.Combine(_,", ") else _ otherwise _, type text}}),
#"Pivoted Column" = Table.Pivot(#"Combine items in list", List.Distinct(#"Combine items in list"[Attribute]), "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",List.Transform(Table.ColumnNames(#"Removed Columns"),each {_,type text}))
in  #"Changed Type"

Upvotes: 2

Related Questions