To connect to a SOAP API in Power Query, you can use the "Web.Contents" function to make a web request to the API, and then use the "Xml.Tables" function to parse the response into a table. Here's an example of how you can do this:
Open Power Query by going to the "Data" tab in Excel and clicking on the "From Other Sources" button, then selecting "From Web".
In the Web dialog box that appears, enter the URL for the SOAP API endpoint, and click the "OK" button.
In the Power Query editor that opens, you'll see a step called "Source" with the formula "= Web.Contents(url)". This step is making the web request to the API.
Next, you'll need to parse the response into a table. You can do this by adding a new step and using the "Xml.Tables" function. For example, you can use the following formula to parse the response into a table called "Data":
Data = Xml.Tables(Source)
Keep in mind that the above formula is just a skeleton and you need to tweak it based on your API structure.
Finally, you can load the table into Excel by going to the "Home" tab in the Power Query editor and clicking the "Close & Apply" button.
In case the API requires authentication, you will need to pass the authentication details in the headers, before making the API call.
You can pass headers as a record in Web.Contents, for example :
Web.Contents("https://example.com", Headers=[Authorization="Basic " & Token])
That's it! You should now be able to work with the data from the SOAP API in Excel.
You may also want to experiment with different features of Power Query to clean and transform the data as needed.
Keep in mind, it might be hard for me to give an accurate answer for your case since different SOAP APIs have different structures, but hopefully, this gives you a general idea of how to connect to a SOAP API in Power Query.