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.
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
