Using Excel to Pull Data With REST and Multiple BAQs

Kinetic ERP
1

I have used Excel in the past to pull in the results of a single BAQ. This works well and gives me flexibility in how I display the information.

Now I want to start with a list of job numbers in Excel and then pull in related data from at least 3 BAQs. I would like it to be dynamic, so that I can update the list of job numbers, then refresh the file and it would pull down new data from the REST API. I am not sure how to go about this part.

Normally, I just create a sheet in Excel and the entire sheet contains the results of my BAQ. I would populate it with a power query in M code. Instead, I want the results to be pulled based on just the jobs that I have listed.

I have used paste/insert from my job list into a BAQ filter. While this would get me there, I am looking for a much more user-friendly approach that doesn’t require manipulating BAQs. Ideally just open the Excel file, verify/update the job list, then refresh the data from Epicor. It should pull down the related data from all the relevant BAQs, and map them up in the final sheet.

I think I need a way to make the values in the Excel range Sheet1 C2:C40, the parameter filter for the BAQ. Is this doable? Am I on the right track? Thank you for your time!
Nate

2

Doable? possibly
@amurdock has an excel sheet template that does some of this though I think its just one BAQ as well. Let me dig it up

1 Like
3

That’s entirely possible in M, but can potentially get sketchy.

Option 1 - Load all the details in a second M query. Join (“merge”) the details on your selected jobs. If the job list your users are providing is in a table, that’s an easy data source to get in M, and there are several ways to do that to deal with how good your users are at breaking tables. Gets a little sketchy if your job detail data is immense.

Option 2 - Start by pulling your user’s inputs from a table. Query your job details in M as a new column. This option gets sketchy if you’re doing this over thousands of rows, because each row will run a simultaneous REST query - it’s good manners to try to not DDoS the server. Much easier to set limits on though. You can select only the top /n/ rows of your user’s jobs list, dropping the rest.

2 Likes
4

This would be a lot of BAQ parameters. Another option might be to leverage the “$filter” option in the REST call. So you would have:

http ://....../BAQname/Data?$filter=JobNum eq 'jobnum1' or JobNum eq 'jobnum2' or JobNum eq 'jobnum3'

Constructing a string such as “JobNum eq ‘jobnum1’ or JobNum eq ‘jobnum2’ or JobNum eq ‘jobnum3’” should be easy from a column of jobnum entries, but I don’t know how you then dynamically attach that string to your REST call in Excel.

2 Likes
5

That would be nice to avoid!

1 Like
6

Try this @NateS :

let   // OData Feed Pull
    Headers = [
        // Add your headers here
    ],
    // Query Parameter(s)
    partsToFind = Table.FirstValue(PartsToFind),
    baseTableData = OData.Feed(p("URI_Protocol")&"://"&p("Server_FQDN")&"/"&p("Epicor_Instance")&"/"&p("API_Version")&"/"&p("ServiceName")&"/"&p("BAQ")&"/"&"?PartsToFind="&partsToFind
        , Headers, [Implementation="2.0"]) 
in 
    baseTableData

let
	PartsToFind = Table.RemoveColumns(
			Table.TransformColumns(
					Table.AddColumn(
							Table.Group(
									Table.AddColumn(
											if(Table.IsEmpty(dt("tPartsToFind"))) then 
													#table(type table [PartNum = 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], "PartNum")), // 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"}) // Remove our temp added columns to to the transposition and aggregation of the parameter(s).
in
	PartsToFind

image
image1782×842 221 KB

EDIT for explanation:
So, create a table somewhere in your workbook and note its name or set one for it. I named mine “tPartsToFind”. (TopRight of Image above) This will allow easy editing of the parameter list items using that named table which I defined and used in the Power M Query dt function provided above. I purposely had it selected to demonstrate the input params. The PartsToFind in greenish is the resultant value of the “let in” table query definition for PartsToFind and results in single concatenated row of the part numbers you can use JobNum instead :slight_smile:

From the image you can also see the results OData Feed of the p(“BAQ”): TempEUH

image
image1163×774 201 KB

Company, PartNum, Description, RowIdent see at bottom of first pic.

Any questions or further explanations please let me know.

3 Likes
7

This looks incredible! I will give a shot soon! Thank you so much!

1 Like
8

Welcome I did kinda post it backwards the first one should be the last to appear as it is the final to pull your data but the second one is the one to prep the excel table into a list of values to append onto the OData query feed as a parameter.

9

If you can write some vba you can have a button on the adds-in tab to launch a macro and read and update Epicor via rest.

These routines are from a macro that checks a customer portal download, create orders and jobs sheets from Epicor validate jobs that are critical and then update those jobs back in Epicor.

Here are a couple of sample subs.
GetOrderData.txt (3.0 KB)

SetCriticalJobs.txt (1.4 KB)

1 Like