Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
huismank
New Member

Extracting values of JSON file with null values

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.

 

 

 

2 REPLIES 2
wdx223_Daniel
Super User
Super User

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]

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.