Forum Discussion
Obsolete Hierarchie cannot be dismissed
Hi Guys
I have the following challenge. I'm getting the data via REST from our DMS system so I can't change the format of the input. I get the data in json or xml format, below I will show the challenge in json.
The issue ist, that we have a redundant layer of key and value:
"data": [
{
"map": [
{
"key": {
"string": "key"
},
"value": {
"string": "Mandatsname"
}
},
{
"key": {
"string": "value"
},
"value": {
"string": "Test Trust"
}
}
]
complete json:
Which is then split into two rows in PowerBI and I loose the link between "Mandatsname" and "Test Trust"
This is the code I am using up until now:
= Table.ExpandListColumn(#"Gefilterte Zeilen", "result.data")
= Table.ExpandListColumn(#"Gefilterte Zeilen1", "result.data.map")
= Table.ExpandRecordColumn(#"Erweiterte result.data.map", "result.data.map", {"key", "value"}, {"result.data.map.key", "result.data.map.value"})
= Table.ExpandRecordColumn(#"Erweiterte result.data.map1", "result.data.map.key", {"string"}, {"result.data.map.key.string"})
= Table.ExpandRecordColumn(#"Erweiterte result.data.map.key", "result.data.map.value", {"string"}, {"result.data.map.value.string"})
4 Replies
{"id":"urn:multiarchive:event:archive.business:132#1-1","revision":{"by":"urn:multiarchive:user:ADMIN#1-1","description":"System Administrator","time":"2022-03-10T09:24:55","published":true},"permissions":[{"NEXTADMIN":["Delete"]},{"ALL":["Access","Create","Delete"]}],"time-stamp":"2022-03-10T09:24:55","name":"Standort vom Anwender geändert","user":"urn:multiarchive:user:ADMIN#1-1","user-description":"System Administrator","target":"urn:multiarchive:item:DOMAREXT:314#1-3","data":[{"map":[{"key":{"string":"key"},"value":{"string":"Mandatsname"}},{"key":{"string":"value"},"value":{"string":"Test Trust"}}]},{"map":[{"key":{"string":"key"},"value":{"string":"Mäppchen"}},{"key":{"string":"value"},"value":{"string":"AIA"}}]},{"map":[{"key":{"string":"key"},"value":{"string":"Standort"}},{"key":{"string":"value"},"value":{"string":"VSB"}}]},{"map":[{"key":{"string":"key"},"value":{"string":"MandatsID"}},{"key":{"string":"value"},"value":{"string":"DOM-106115-000"}}]}],"origin":"User Interface","category":"MODIFIED"}sorry couldn't insert the entire json into the question above, this is the entire entry I get via rest
I found a work around which works.
1) I generated an index column.
= Table.AddIndexColumn(#"Erweiterte result.data.map.value", "Index",1)
2) I duplicate the query
3) I add another column with the index plus 1 in one query
= Table.AddColumn(#"Gefilterte Zeilen2", "Entry.Index", each [Index] + 1)
4) I joined both tables on the Index to Entry.Index
= Table.NestedJoin(#"Umbenannte Spalten", {"Entry.Index"}, Maeppchen_Aenderungen_values_Hilfstabelle, {"Index"}, "Maeppchen_Aenderungen_values", JoinKind.LeftOuter)
5) I disabled all columns I don't need
However this seems like a very inelegant way of doing it.
Hi EmpMsgAEG ,
What type of effects are you wanted? Transform them to one row instead of split into two rows with key value part?
Can you please explain more about these and share the expected results? They will help us cleary your requirement and test to coding formulas.
Regards,
Xiaoxin Shen
exactly, so I need one row displaying the key and the value, where as if I use the built in function it splits the top hierarchie into two rows and then splits it into two columns.
As I mentioned in the other reply I found a solution where I create an index, then duplicate the result, add +1 to one index and then do an innerjoin to combine the information on one row. This just seems very inelegant and I am unsure if it will become to slow as soon as the solution goes live with a lot of data.
