Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 ??
Solved! Go to Solution.
Yes!
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??
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
I think @DataChant is right - you probably need to replace something like Xml.Tables with Json.Document and the code should work
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 --------------------------------
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
69 | |
64 | |
50 | |
36 | |
26 |
User | Count |
---|---|
86 | |
55 | |
44 | |
43 | |
36 |