I have a JSON file of ACS data from the Census API that I am trying to read into Excel. The Census uses a non-standard version of JSON. It's essentially just a text file. I am not experienced with JSON files or converting text files to usable formats.
I've been reading in the JSON file and converting to a table without a delimiter because adding a comma delimiter gives me an error. This is the code:
= Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
I then have to extract the values, the columns are not being read automatically. I extract using a comma:
= Table.TransformColumns(#"Converted to Table", {"Column1", each Text.Combine(List.Transform(_, Text.From), ","), type text})
The problem is that the table contains null values. These null values are not quoted, whereas all the valid values are. The result is that the null values are not extracted. Most list entries list 12 comma seperated values for 12 variables, but for entries with null values there are less.
When I expand columns to a table:
= Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}, {"Column1.7", type text}, {"Column1.8", type text}, {"Column1.9", type text}, {"Column1.10", type text}, {"Column1.11", type text}, {"Column1.12", type text}})
The rows that contained null values are incorrect. All entries in rows that originally contained null values are incorrect for each variable after the missing null value.
Is there a way to tell power query to extract those null values even though they are not quoted? I tried editing the JSON file to add quotes around each null value, but that results in power query editor not being able to read the file at all.
Thanks in advance.
