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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TSmith0702
New Member

Need help please

Hello,

 

I am a complete newbie to Power Query so please bear this in mind. I have a query that's taking data from our CRM system to give me a £ figure for our sales in 'today'. Obviously our sales in 'today' is going to equal zero at the beginning of each day, and the list will have no rows. I am getting the error 'we cannot convert the value null to type list'.

 

Sorry if that's not clear, but is there any way around this please?

 

Thanks

4 REPLIES 4
Anonymous
Not applicable

Can you explain your process of loading the data little more?  Does the process still work for when you pull in sales from a day that isn't today? What does the data look like in the Source step of your applied steps? There may be a problem with how the value is being loaded. 

Here's an example with a sample JSON file (source: https://www.learningcontainer.com/sample-json-file/)

1) Load data, right-click list and select into table

hunterfeldman_0-1598362754689.png

 

2) Select expand icon and choose Expand to New Rows 

hunterfeldman_0-1598362979279.png

 

3) Click the expand icon again and select all the columns 

hunterfeldman_1-1598363053771.png

 

4) Result 

hunterfeldman_3-1598363098688.png

 

2.b) Alternatively, I could have selected Extract Values in Step 2 

hunterfeldman_4-1598363202942.png

 

3.b) Select no delimiter 

hunterfeldman_5-1598363258664.png

 

4.b) Which leads to this error

hunterfeldman_7-1598363292436.png

hunterfeldman_8-1598363308762.png




If you don't think it has to do with any of the extraction/conversion process I would try putting some sort of logic statment before the Table.FromList step. You could use List.IsEmpty beforehand to check that there is contents within the list. Here's some material on List functions in M and a good series on more advanced techniques in M. 

https://docs.microsoft.com/en-us/powerquery-m/list-functions

https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressi...

Fowmy
Super User
Super User

@TSmith0702 

Can you share some sample data/Power Query Code and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@Fowmy 

This is copied directly from the 'advanced editor'.

 

let
Source = Json.Document(Web.Contents("https://api.pipedrive.com/v1/deals?filter_id=49&status=won&start=0&api_token=___", [Headers=[Accept=" ""application/json"""]])),
data = Source[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "title", "value", "status", "won_time", "person_name", "org_name", "formatted_value", "formatted_weighted_value", "weighted_value_currency", "owner_name"}, {"Column1.id", "Column1.title", "Column1.value", "Column1.status", "Column1.won_time", "Column1.person_name", "Column1.org_name", "Column1.formatted_value", "Column1.formatted_weighted_value", "Column1.weighted_value_currency", "Column1.owner_name"})
in
#"Expanded Column1"

 

pls note i have removed the API token

 

 

I'm just trying to get a simple list in excel from the data. Excuse my ignorance

@TSmith0702 

I cannot diagnose without seeing the error,  Can you share the screenshot how the data looks like before #"Converted to Table"  step?

________________________

If my answer was helpful, please mark this post as a solution, this will also help others!.

Click on the Thumbs-Up icon if you like this reply 🙂



YouTube
LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors