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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
ukantkumar
Helper I
Helper I

rest api json issue column issue

I am calling rest API in power bi desktop, and I am getting JSON response but the problem is Each key and value pair coming Separate separate column, but I need to convert in the table ?? how ?? 

1 ACCEPTED SOLUTION

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.

View solution in original post

8 REPLIES 8
ukantkumar
Helper I
Helper I

Yes!

DataChant
Post Prodigy
Post Prodigy

From the screenshot you shared it seems that your query didn’t recognize the format as json. Can you share the M expression (from the Advanced Editor)? This should be an easy fix.

let
Source = Csv.Document(Web.Contents("http://localhost:8080/ext/rest/specialStr/powerbi/1/aru/submitter/58/756/2018-10-30"),[Delimiter=",", Columns=2540, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}, {"Column42", type text}, {"Column43", type text}, {"Column44", type text}, {"Column45", type text}, {"Column46", type text}, {"Column47", type text}, {"Column48", type text}, {"Column49", type text}, {"Column50", type text}, {"Column51", type text}, {"Column52", type text}, {"Column53", type text}, {"Column54", type text}, {"Column55", type text}, {"Column56", type text}, {"Column57", type text}, {"Column58", type text}, {"Column59", type text}, {"Column60", type text}, {"Column61", type text}, {"Column62", type text}, {"Column63", type text}, {"Column64", type text}, {"Column65", type text}, {"Column66", type text}, {"Column67", type text}, {"Column68", type text}, {"Column69", type text}, {"Column70", type text}, {"Column71", type text}, {"Column72", type text}, {"Column73", type text}, {"Column74", type text}, {"Column75", type text}, {"Column76", type text}, {"Column77", type text}, {"Column78", type text}, {"Column79", type text}, {"Column80", type text}, {"Column81", type text}, {"Column82", type text}, {"Column83", type text}, {"Column84", type text}, {"Column85", type text}, {"Column86", type text}, {"Column87", type text}, {"Column88", type text}, {"Column89", type text}, {"Column90", type text}, {"Column91", type text}, {"Column92", type text}, {"Column93", type text}, {"Column94", type text}, {"Column95", type text}, {"Column96", type text}, {"Column97", type text}, {"Column98", type text}, {"Column99", type text}, {"Column100", type text}, {"Column101", type text}, {"Column102", type text}, {"Column103", type text}, {"Column104", type text}, {"Column105", type text}, {"Column106", type text}, {"Column107", type text}, {"Column108", type text}, {"Column109", type text}, {"Column110", type text}, {"Column111", type text}, {"Column112", type text}, {"Column113", type text}, {"Column114", type text}, {"Column115", type text}, {"Column116", type text}, {"Column117", type text}, {"Column118", type text}, {"Column119", type text}, {"Column120", type text}, {"Column121", type text}, {"Column122", type text}, {"Column123", type text}, {"Column124", type text}, {"Column125", type text}, {"Column126", type text}, {"Column127", type text}, {"Column128", type text}, {"Column129", type text}, {"Column130", type text}, {"Column131", type text}, {"Column132", type text}, {"Column133", type text}, {"Column134", type text},  {"Column455", type text}, {"Column456", type text}, {"Column457", type text}, {"Column458", type text}, {"Column459", type text}, {"Column460", type text}, {"Column461", type text}, {"Column462", type text}, {"Column463", type text}, {"Column464", type text}, {" type text}, {"Column2536", type text}, {"Column2537", type text}, {"Column2538", type text}, {"Column2539", type text}, {"Column2540", type text}})
in
#"Changed Type"

 

 

like that coming in M exoression 1 to 2540 column??

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.

Hi I tried all above steps but i am getting this error after step no 5, mean right click and select Jason, can you help me in this 

DataFormat.Error: We found an invalid value in JSON input. Only 'true', 'false', or 'null' are supported.
Details:
Value=n
Position=998

Stachu
Community Champion
Community Champion

I think @DataChant is right - you probably need to replace something like Xml.Tables with Json.Document and the code should work



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Stachu
Community Champion
Community Champion

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 

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Each key and value pair coming column wise,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 --------------------------------



 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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