Forum Discussion
Cannot Expand Null List
I am getting heavily nested data from an api and need to expand all the columns. I followed this guide to stop creating new rows from expanding them and it worked well. The problem is some of the lists are null. They all appear as type List after zipping, but some have null values. In the editor I can expand them to records and then expand them further, but for some reason when I try to load the data into the report I get the below error whenever I try to change to records and load in the report.
OLE DB or ODBC error: [Expression.Error] We cannot convert the value null to type List..
Optimally I would delete the rows with null values since they have no data, but I cannot figure out how to tell if one of the list items contains no data and delete it.
Function below (It's a closed API):
let
Source = Json.Document(Web.Contents(www.API.com, [Headers=[Authorization="Token"]])),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"webPathId", "milestones"}, {"Column1.webPathId", "Column1.milestones"}),
#"Expanded Column1.milestones" = Table.ExpandListColumn(#"Expanded Column1", "Column1.milestones"),
#"Expanded Column1.milestones1" = Table.ExpandRecordColumn(#"Expanded Column1.milestones", "Column1.milestones", {"networkTiming", "serverTiming", "browserTiming", "apdexScore", "basePageSize", "statusCode"}, {"networkTiming", "serverTiming", "browserTiming", "apdexScore", "basePageSize", "statusCode"}),
#"Merged Columns" = Table.CombineColumns(#"Expanded Column1.milestones1",{"networkTiming", "serverTiming", "browserTiming", "apdexScore", "basePageSize", "statusCode"}, each List.Transform(List.Zip(_), each Record.FromList(_,{"networkTiming", "serverTiming", "browserTiming", "apdexScore", "basePageSize", "statusCode"})),"Merged"),
#"Expanded Merged" = Table.ExpandListColumn(#"Merged Columns", "Merged")
in
#"Expanded Merged"
Well, that was it! Finally got it to load. Thanks for the help Greg_Deckler . Even though that wasn't the answer it was the only thing that allowed me to find the issue! Thanks!
9 Replies
I had a similar issue to this, I will try to dig up my solution. But, in essence, what I did was make the expansion/conversion conditional by throwing in an if then else statemetn into the each clause so if null then null otherwise convert
Thanks Greg_Deckler, I've been having issues even telling whether a list is null since it's nested. I'm fairly new to dax though, so I may be doing something wrong. It's been difficult to track down the issue since it only occurs when I try to load it in the report. It's strange that it works fine in the editor.
Oh, this is not DAX, this is Power Query and the error you are getting is a Power Query error during data load. It is likely not happening while you are in the editor because the first rows do not exhibit a null but later rows do. Let me see if I can get you an example.
