Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there. New to power query so hoping someone can help me with the above error. I've had a look round but can't find the exact thing I'm looking for. Have also had a play about with MissingField.Ignore but just can't quite get it to work but I suspect it's a simple fix?
I have built a function which scrapes a few different web pages. The below code is for one url rather than the function. These web pages are constantly updating before they are taken off the website. At this point the table becomes blank so therefore column1 obviously cannot be found. Please see the below code. It has been compiled through the power query interface so assume it's a bit messy. However, the error pops up when I try to expand column1. Is there a simple way to just say if column1 does not exist stop? Or in the step before if table is blank stop? Because it is part of a function it stops everything running whereas I would prefer for it to just skip that individual query. Or change all values in the table to null which I can filter at a later stage?
Thanks in advance for any help!
let Source = Json.Document(Web.Contents("https://www.spreadex.com/sports/mobile/api/api/Model/SubscribeModel?modelRef=sports/page/all/spr/672401")), model = Source[model], panels = model[panels], #"Converted to Table" = Table.FromList(panels, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"panelItems", "model", "name"}, {"Column1.panelItems", "Column1.model", "Column1.name"}), #"Expanded Column1.model" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.model", {"selections"}, {"Column1.model.selections"}), #"Expanded Column1.model.selections" = Table.ExpandListColumn(#"Expanded Column1.model", "Column1.model.selections"), #"Expanded Column1.model.selections1" = Table.ExpandRecordColumn(#"Expanded Column1.model.selections", "Column1.model.selections", {"name", "price"}, {"Column1.model.selections.name", "Column1.model.selections.price"}), #"Expanded Column1.panelItems" = Table.ExpandListColumn(#"Expanded Column1.model.selections1", "Column1.panelItems"), #"Expanded Column1.model.selections.price" = Table.ExpandRecordColumn(#"Expanded Column1.panelItems", "Column1.model.selections.price", {"buy", "sell", "status"}, {"Column1.model.selections.price.buy", "Column1.model.selections.price.sell", "Column1.model.selections.price.status"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1.model.selections.price",{"Column1.panelItems"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Column1.name", "Column1.model.selections.name", "Column1.model.selections.price.sell", "Column1.model.selections.price.buy"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Column1.model.selections.name", "Market"}, {"Column1.model.selections.price.buy", "Buy"}, {"Column1.model.selections.price.sell", "Sell"}, {"Column1.name", "Section"}}), #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns",{"Section", "Market", "Sell", "Buy", "Column1.model.selections.price.status"}), #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns1",{{"Column1.model.selections.price.status", "Status"}}) in #"Renamed Columns1"
Solved! Go to Solution.
Hi there,
easiest is to add a line of code at the end:
let Source = Json.Document(Web.Contents("https://www.spreadex.com/sports/mobile/api/api/Model/SubscribeModel?modelRef=sports/page/all/spr/672401")), model = Source[model], panels = model[panels], #"Converted to Table" = Table.FromList(panels, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"panelItems", "model", "name"}, {"Column1.panelItems", "Column1.model", "Column1.name"}), #"Expanded Column1.model" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.model", {"selections"}, {"Column1.model.selections"}), #"Expanded Column1.model.selections" = Table.ExpandListColumn(#"Expanded Column1.model", "Column1.model.selections"), #"Expanded Column1.model.selections1" = Table.ExpandRecordColumn(#"Expanded Column1.model.selections", "Column1.model.selections", {"name", "price"}, {"Column1.model.selections.name", "Column1.model.selections.price"}), #"Expanded Column1.panelItems" = Table.ExpandListColumn(#"Expanded Column1.model.selections1", "Column1.panelItems"), #"Expanded Column1.model.selections.price" = Table.ExpandRecordColumn(#"Expanded Column1.panelItems", "Column1.model.selections.price", {"buy", "sell", "status"}, {"Column1.model.selections.price.buy", "Column1.model.selections.price.sell", "Column1.model.selections.price.status"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1.model.selections.price",{"Column1.panelItems"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Column1.name", "Column1.model.selections.name", "Column1.model.selections.price.sell", "Column1.model.selections.price.buy"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Column1.model.selections.name", "Market"}, {"Column1.model.selections.price.buy", "Buy"}, {"Column1.model.selections.price.sell", "Sell"}, {"Column1.name", "Section"}}), #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns",{"Section", "Market", "Sell", "Buy", "Column1.model.selections.price.status"}), #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns1",{{"Column1.model.selections.price.status", "Status"}}), ErrorHandling = try #"Expanded Column1" otherwise null in ErrorHandling
"try #"Expanded Column1" otherwise null" will check the step which can cause the error and returns null (or anything else that you replace that with) in case of error.
Because M is partially lazy, there is a good chance that in case of error all the subsequent steps are not executed and performance will not be affected negatively.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi there,
easiest is to add a line of code at the end:
let Source = Json.Document(Web.Contents("https://www.spreadex.com/sports/mobile/api/api/Model/SubscribeModel?modelRef=sports/page/all/spr/672401")), model = Source[model], panels = model[panels], #"Converted to Table" = Table.FromList(panels, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"panelItems", "model", "name"}, {"Column1.panelItems", "Column1.model", "Column1.name"}), #"Expanded Column1.model" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.model", {"selections"}, {"Column1.model.selections"}), #"Expanded Column1.model.selections" = Table.ExpandListColumn(#"Expanded Column1.model", "Column1.model.selections"), #"Expanded Column1.model.selections1" = Table.ExpandRecordColumn(#"Expanded Column1.model.selections", "Column1.model.selections", {"name", "price"}, {"Column1.model.selections.name", "Column1.model.selections.price"}), #"Expanded Column1.panelItems" = Table.ExpandListColumn(#"Expanded Column1.model.selections1", "Column1.panelItems"), #"Expanded Column1.model.selections.price" = Table.ExpandRecordColumn(#"Expanded Column1.panelItems", "Column1.model.selections.price", {"buy", "sell", "status"}, {"Column1.model.selections.price.buy", "Column1.model.selections.price.sell", "Column1.model.selections.price.status"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1.model.selections.price",{"Column1.panelItems"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Column1.name", "Column1.model.selections.name", "Column1.model.selections.price.sell", "Column1.model.selections.price.buy"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Column1.model.selections.name", "Market"}, {"Column1.model.selections.price.buy", "Buy"}, {"Column1.model.selections.price.sell", "Sell"}, {"Column1.name", "Section"}}), #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns",{"Section", "Market", "Sell", "Buy", "Column1.model.selections.price.status"}), #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns1",{{"Column1.model.selections.price.status", "Status"}}), ErrorHandling = try #"Expanded Column1" otherwise null in ErrorHandling
"try #"Expanded Column1" otherwise null" will check the step which can cause the error and returns null (or anything else that you replace that with) in case of error.
Because M is partially lazy, there is a good chance that in case of error all the subsequent steps are not executed and performance will not be affected negatively.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |