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 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.
Text.Combine(List.Transform(_, each Text.From(_)??""), ",")
Thanks. When I enter this in as
= Table.TransformColumns(#"Converted to Table", {"Column1", each Text.Combine(List.Transform(_, Text.From(_)??""), ","), type text})
I get an error saying Expression.Error: We cannot convert a value of type List to type Text.
Details:
Value=[List]
Type=[Type]