Nested/Multidimensional JSON

[Anonymous]

Hello, 

I am using data from a web API that returns nested JSON. When I expand the nested data, I lose my unique identifiers because it expands the items into new rows rather than columns.

This is what some of the columns look like when I have expanded it the first time:

This is what it looks like after I expand the second row to new rows (I've tried extracting values but then get an error that says "We cannot convert a value of type Record to type Text"):

What I need to have happen instead is get nine new columns, one for each key ("tag_user_owner", "tag_user_AutoPatch", "tag_user_Environment",... etc.) with the corresponding values in the rows, aligning with each ID. 

I've tried reading through other posts with people having the same issue, but can't get their solutions to work for me. I don't understand power query language very well which is probably why I'm getting stuck with the other solutions. Here are the current steps in the advanced editor (I've already done some cleaning up):

let
    Source = Json.Document(Web.Contents("https://api.cloudability.com/v3/rightsizing/aws/recommendations/ec2?basis=cost&duration=thirty-day&filters=recommendations.defaultsOrder==1&maxRecsPerResource=1&sort=-recommendations.savings&sortField=recommendations.savings&sortOrder=desc&source=datadog,cloudwatch&vendorAccountIds=&viewId=0")),
    result = Source[result],
    #"Converted to Table" = Table.FromList(result, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"service", "name", "resourceIdentifier", "vendorAccountId", "tags", "nodeType", "totalSpend", "recommendations"}, {"Column1.service", "Column1.name", "Column1.resourceIdentifier", "Column1.vendorAccountId", "Column1.tags", "Column1.nodeType", "Column1.totalSpend", "Column1.recommendations"}),
    #"Expanded Column1.recommendations" = Table.ExpandListColumn(#"Expanded Column2", "Column1.recommendations"),
    #"Expanded Column1.recommendations1" = Table.ExpandRecordColumn(#"Expanded Column1.recommendations", "Column1.recommendations", {"action", "nodeType", "savings"}, {"Column1.recommendations.action", "Column1.recommendations.nodeType", "Column1.recommendations.savings"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Column1.recommendations1", each ([Column1.recommendations.action] <> "No Action")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1.service", "Service"}, {"Column1.name", "Name"}, {"Column1.resourceIdentifier", "Resource ID"}, {"Column1.vendorAccountId", "Account ID"}, {"Column1.nodeType", "Current Node Type"}, {"Column1.totalSpend", "Current Spend"}, {"Column1.recommendations.action", "Action"}, {"Column1.recommendations.nodeType", "New Node Type"}, {"Column1.recommendations.savings", "Savings"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Service", type text}, {"Name", type text}, {"Resource ID", type text}, {"Account ID", type text}, {"Current Node Type", type text}, {"Current Spend", Currency.Type}, {"Action", type text}, {"New Node Type", type text}, {"Savings", Currency.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Account ID"}, #"Account to Company Lookup", {"Account ID"}, "Account to Company Lookup", JoinKind.LeftOuter),
    #"Expanded Account to Company Lookup" = Table.ExpandTableColumn(#"Merged Queries", "Account to Company Lookup", {"Company Name"}, {"Account to Company Lookup.Company Name"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Account to Company Lookup","ec2-recs","EC2",Replacer.ReplaceText,{"Service"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Replaced Value",{"Service", "Name", "Resource ID", "Account ID", "Action", "Current Node Type", "New Node Type", "Current Spend", "Savings", "Account to Company Lookup.Company Name"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "Optimized Spend", each [Current Spend]-[Savings]),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom",{"Service", "Name", "Resource ID", "Account ID", "Action", "Current Node Type", "New Node Type", "Current Spend", "Optimized Spend", "Savings", "Account to Company Lookup.Company Name"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Optimized Spend", Currency.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Account to Company Lookup.Company Name", "Company Name"}}),
    #"Reordered Columns2" = Table.ReorderColumns(#"Renamed Columns1",{"Service", "Name", "Account ID", "Resource ID", "Column1.tags", "Action", "Current Node Type", "New Node Type", "Current Spend", "Optimized Spend", "Savings", "Company Name"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns2",{{"Resource ID", "ID"}})
in
    #"Renamed Columns2"

[Stachu · Accepted solution]

1) select Column.1.tags.vendorkey
2) go to the Transform in the ribbon and press Pivot Column

3) the dialog window will pop up, set the vendorValue as a value, and in the Advanced select Don't aggregate