Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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 --------------------------------
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
71 | |
56 | |
39 | |
35 |
User | Count |
---|---|
66 | |
66 | |
59 | |
53 | |
45 |