Using Excel to Pull Data With REST and Multiple BAQs

Kinetic ERP
21

Yes, I have that table named properly in Excel.

22

I misread your response sorry.

image

image
image1483×616 55.3 KB

23

I’m so confused.

24

Ok the tPartsToFind is your input table
PartsToFind is the created one liner (Table from Query and named the same on mine) for the OData parameter LIST.

image
image1580×591 52.5 KB

25

I can get the odata to pull if I remove the parameter:

    baseTableData = OData.Feed("https://MYSERVER/api/v2/odata/VTAERO/BaqSvc/JobDetails/Data?", Headers, [Implementation="2.0"])

But If I add in the parameter segment at the end I get an errror:

    baseTableData = OData.Feed("https://MYSERVER/api/v2/odata/VTAERO/BaqSvc/JobDetails/Data?/"&"?PartsToFind=" &partsToFind, Headers, [Implementation="2.0"])

Error: Formula.Firewall: Query ‘oDataPull’ (step ‘baseTableData’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

26

Ok, you have 2 ‘?’ in your query so that not gonna work.
URL with data being sent back is ?{Item}={Value}&{NextItem}={Value}

I will say your URL should look like this:

baseTableData = OData.Feed("https://MYSERVER/api/v2/odata/VTAERO/BaqSvc/JobDetails/Data"&"?PartsToFind=" &partsToFind, Headers, [Implementation="2.0"])

image
image1163×774 201 KB

If your Parameter is identified the same way.

image
image1035×405 69.5 KB

image
image1307×554 114 KB

27

Capture45g235g245g
Capture45g235g245g1600×1015 211 KB

Capture234f2wf
Capture234f2wf1608×694 127 KB
I think I got it all right. I changed the name to PartsToFind1, and updated it throughout. Still getting that error when I apply the parameter. It looks like I have the BAQ and parameter correct.

28

NVM so is mine…

29

I think this might fix you up. Combine them.

Try:

let   // OData Feed Pull
    // Encode Basic Authentication manually (username:password in Base64) : Change appropriately
    Base64Auth = "Basic " & Binary.ToText(Text.ToBinary("datapuller:mypass"), BinaryEncoding.Base64),

    // API Key : Change appropriately
    ApiKey = "mykey",
    Headers = [
        #"Authorization" = Base64Auth,
        #"X-API-Key" = ApiKey,
        #"Content-Type" = "text/plain"
    ],
    // Query Parameter(s)
	partsToFind = Table.FirstValue(Table.RemoveColumns(
			Table.TransformColumns(
					Table.AddColumn(
							Table.Group(
									Table.AddColumn(
											if(Table.IsEmpty(dt("tPartsToFind"))) then 
													#table(type table [PartNumber = text], {{"Part1"},{"Part2"},{"Part3...etc"}}) // You could use this versus a named table
											else
													dt("tPartsToFind") // Use my named table called tPartsToFind
											, "GrpBy", each "EZ") // Add pseudo aggregator column GrpBy to each row
									, {"GrpBy"}, {{"PnTable", each _, type table [PartNum=text,GrpBy=text]}}) // Group into table
							, "PartsToFind", each Table.Column([PnTable], "PartNumber")), // Use all of our PartNums in our pseudo group table into list
					{"PartsToFind", each Text.Combine(List.Transform(_,Text.From),"&PartsToFind="), type text}) // Aggregate each list item with a new copy of the &param= dates will need more here
			,{"GrpBy", "PnTable"})),
    baseTableData = OData.Feed("https://myserver/api/v2/odata/VTAERO/BaqSvc/JobDetails/Data?PartsToFind=" &partsToFind, Headers, [Implementation="2.0"]) 
in 
    baseTableData
30

It doesn’t like that second let. Error: Token identifier expected.

31

Opps ya that does not belong there another typo…
Recopy now lol I fixed my typos.
And readded the missing /Data LOL opps

32

This did the trick! Thank you so much for your help! I am not sure what was causing the issues before, but this works great!

let   // OData Feed Pull
    // Encode Basic Authentication manually (username:password in Base64) : Change appropriately
    Base64Auth = "Basic " & Binary.ToText(Text.ToBinary("datapuller:MYPASSWORD"), BinaryEncoding.Base64),

    // API Key : Change appropriately
    ApiKey = "MYAPIKEY",
    Headers = [
        #"Authorization" = Base64Auth,
        #"X-API-Key" = ApiKey,
        #"Content-Type" = "text/plain"
    ],
    // Query Parameter(s)
	partsToFind = Table.FirstValue(Table.RemoveColumns(
			Table.TransformColumns(
					Table.AddColumn(
							Table.Group(
									Table.AddColumn(
											if(Table.IsEmpty(dt("tPartsToFind"))) then 
													#table(type table [PartNumber = text], {{"Part1"},{"Part2"},{"Part3...etc"}}) // You could use this versus a named table
											else
													dt("tPartsToFind") // Use my named table called tPartsToFind
											, "GrpBy", each "EZ") // Add pseudo aggregator column GrpBy to each row
									, {"GrpBy"}, {{"PnTable", each _, type table [PartNum=text,GrpBy=text]}}) // Group into table
							, "PartsToFind", each Table.Column([PnTable], "PartNumber")), // Use all of our PartNums in our pseudo group table into list
					{"PartsToFind", each Text.Combine(List.Transform(_,Text.From),"&PartsToFind="), type text}) // Aggregate each list item with a new copy of the &param= dates will need more here
			,{"GrpBy", "PnTable"})),
	URL = "https://MYSERVER/api/v2/odata/MYCOMPANY/BaqSvc/JobDetails/Data"&"?PartsToFind=" &partsToFind,
    baseTableData = OData.Feed(URL, Headers, [Implementation="2.0"])
in
    baseTableData
33

Glad to help I guess it was just mad and felt like it was pulling data after data and did not like that here is what led me to combine it.

Behind the scenes of the Data Privacy Firewall - Power Query | Microsoft Learn