Forum Discussion
rest api json issue column issue
- I will try to explain the fix using the UI of Power Query Editor first to make it easier. Let me know if it works.
1- In Applied Steps, delete the Changed Type step.
2- Open Advanced Editor
3- in the line that starts with “Source =“, delete “CSV.Document(“ and then delete the second argument of CSV.Document. In other words, keep only the Web.Contents function followed by the comma at the end.
4- Don’t make the change in the formula bar. Only in Advanced Editor.
5- In the preview pane you should now see a web file icon. Right click it and in the shortcut menu select Json.
6- Drill down to the record object of the “Item” field.
7- Convert the list into a table.
8- Expand Column1.
can you share sample data?
there is this:
https://msdn.microsoft.com/en-us/query-bi/m/table-fromcolumns
as well as this:
https://msdn.microsoft.com/en-us/query-bi/m/list-zip
but without more concrete example I cannot tell if it's applicable
Each key and value pair coming column wise,
http://localhost:8080/ext/rest/specialStr/powerbi/1/kumar/submitter/58/756/2018-10-30
This is the URL, I am getting JSON data response
{
"success": true,
"Item": [
{
"projectId": 1,
"specDprDate": "2018-07-02",
"dmaId": 58,
"dprBoqId": 756,
"specBoqId": 420,
"uom": "Lot",
"specParentId": 0,
"clientSpecBoqId": "644",
"boqName": "STP - 15 MLD",
"weightage": 100,
"plannedCost": 246147658.81,
"exeuted": 0,
"exeApprover": 0,
"executedCost": 0,
"exeApproverCost": 0,
"cummExeApproverCost": 0,
"cummExecutedApprover": 0,
"cummExecuted": 66.44500000000002,
"cummExeCost": 163552811.89630455
},
{
"projectId": 1,
"specDprDate": "2018-07-02",
"dmaId": 58,
"dprBoqId": 756,
"specBoqId": 421,
"uom": "Lot",
"specParentId": 420,
"clientSpecBoqId": "644 a",
"boqName": " DESIGN ,DRAWINGS & Documentation",
"weightage": 6,
"plannedCost": 14768859.53,
"exeuted": 0,
"exeApprover": 0,
"executedCost": 0,
"exeApproverCost": 0,
"cummExeApproverCost": 0,
"cummExecutedApprover": 0,
"cummExecuted": 95.00000000000001,
"cummExeCost": 14030416.553500002
},
{
"projectId": 1,
"specDprDate": "2018-07-02",
"dmaId": 58,
"dprBoqId": 756,
"specBoqId": 422,
"uom": "Lot",
"specParentId": 421,
"clientSpecBoqId": "644 a.1",
"boqName": "PROCESS DESIGN & DRAWINGS",
"weightage": 2.5,
"plannedCost": 6153691.47,
"exeuted": 0,
"exeApprover": 0,
"executedCost": 0,
"exeApproverCost": 0,
"cummExeApproverCost": 0,
"cummExecutedApprover": 0,
"cummExecuted": 100,
"cummExeCost": 6153691.47
},
{
"projectId": 1,
"specDprDate": "2018-07-02",
"dmaId": 58,
"dprBoqId": 756,
"specBoqId": 423,
"uom": "Lot",
"specParentId": 421,
"clientSpecBoqId": "644 a.2",
"boqName": "\"ARCHITECTURAL & STRUCTURAL\r DESIGNS AND DRAWINGS\r ",
"weightage": 1.2,
"plannedCost": 2953771.91,
"exeuted": 0,
"exeApprover": 0,
"executedCost": 0,
"exeApproverCost": 0,
"cummExeApproverCost": 0,
"cummExecutedApprover": 0,
"cummExecuted": 100,
"cummExeCost": 2953771.91
},
{
"projectId": 1,
"specDprDate": "2018-07-02",
"dmaId": 58,
"dprBoqId": 756,
"specBoqId": 424,
"uom": "Lot",
"specParentId": 421,
"clientSpecBoqId": "644 a.3",
"boqName": "\"MECHANICAL INCLUDING PIPING ELECTRICAL AND INSTRUMENTATION & CONTROL and design drawings\r ",
"weightage": 2,
"plannedCost": 4922953.18,
"exeuted": 0,
"exeApprover": 0,
"executedCost": 0,
"exeApproverCost": 0,
"cummExeApproverCost": 0,
"cummExecutedApprover": 0,
"cummExecuted": 100,
"cummExeCost": 4922953.18
},
{
"projectId": 1,
"specDprDate": "2018-07-02",
"dmaId": 58,
"dprBoqId": 756,
"specBoqId": 425,
"uom": "Lot",
"specParentId": 421,
"clientSpecBoqId": "644 a.4",
"boqName": "As built drawings for the plant\r ",
"weightage": 0.1,
"plannedCost": 246147.66,
"exeuted": 0,
"exeApprover": 0,
"executedCost": 0,
"exeApproverCost": 0,
"cummExeApproverCost": 0,
"cummExecutedApprover": 0,
"cummExecuted": 0,
"cummExeCost": 0
},
{
"projectId": 1,
"specDprDate": "2018-07-02",
"dmaId": 58,
"dprBoqId": 756,
"specBoqId": 426,
"uom": "Lot",
"specParentId": 421,
"clientSpecBoqId": "644 a.5",
"boqName": "operation and maintenance manual\n",
"weightage": 0.1,
"plannedCost": 246147.66,
"exeuted": 0,
"exeApprover": 0,
"executedCost": 0,
"exeApproverCost": 0,
"cummExeApproverCost": 0,
"cummExecutedApprover": 0,
"cummExecuted": 0,
"cummExeCost": 0
},
{
"projectId": 1,
"specDprDate": "2018-07-02",
"dmaId": 58,
"dprBoqId": 756,
"specBoqId": 427,
"uom": "Lot",
"specParentId": 421,
"clientSpecBoqId": "644 a.6",
"boqName": " training programme manual",
"weightage": 0.1,
"plannedCost": 246147.66,
"exeuted": 0,
"exeApprover": 0,
"executedCost": 0,
"exeApproverCost": 0,
"cummExeApproverCost": 0,
"cummExecutedApprover": 0,
"cummExecuted": 0,
"cummExeCost": 0
},
{
"projectId": 1,
"specDprDate": "2018-07-02",
"dmaId": 58,
"dprBoqId": 756,
"specBoqId": 428,
"uom": "Lot",
"specParentId": 420,
"clientSpecBoqId": "644 b",
"boqName": "civil works",
"weightage": 42,
"plannedCost": 103382016.71,
"exeuted": 0,
"exeApprover": 0,
"executedCost": 0,
"exeApproverCost": 0,
"cummExeApproverCost": 0,
"cummExecutedApprover": 0,
"cummExecuted": 65.98809523809526,
"cummExeCost": 68219823.64565834
},
{
"projectId": 1,
"specDprDate": "2018-07-02",
"dmaId": 58,
"dprBoqId": 756,
"specBoqId": 429,
"uom": "Lot",
"specParentId": 428,
"clientSpecBoqId": "644 b.1",
"boqName": "inlet works",
"weightage": 2,
"plannedCost": 4922953.18,
"exeuted": 0,
"exeApprover": 0,
"executedCost": 0,
"exeApproverCost": 0,
"cummExeApproverCost": 0,
"cummExecutedApprover": 0,
"cummExecuted": 0,
"cummExeCost": 0
},
{
"projectId": 1,
"specDprDate": "2018-07-02",
"dmaId": 58,
"dprBoqId": 756,
"specBoqId": 430,
"uom": "Lot",
"specParentId": 429,
"clientSpecBoqId": "644 b.1.1",
"boqName": "\"inlet chamber **bleep** distributionchamber \n",
"weightage": 0.25,
"plannedCost": 615369.15,
"exeuted": 0,
"exeApprover": 0,
"executedCost": 0,
"exeApproverCost": 0,
"cummExeApproverCost": 0,
"cummExecutedApprover": 0,
"cummExecuted": 0,
"cummExeCost": 0
},
{
"projectId": 1,
"specDprDate": "2018-07-03",
"dmaId": 58,
"dprBoqId": 756,
"specBoqId": 1631,
"uom": "Lot",
"specParentId": 430,
"clientSpecBoqId": "644 b.1.1.1",
"boqName": "Earth work Excavation",
"weightage": 0.02,
"plannedCost": 61536.91,
"exeuted": 0,
"exeApprover": 0,
"executedCost": 0,
"exeApproverCost": 0,
"cummExeApproverCost": 0,
"cummExecutedApprover": 0,
"cummExecuted": 0,
"cummExeCost": 0
},
this is the few json sample i have more --------------------------------