Advanced query for transactions

[Roger_Van_Decraen]

I want to create an advanced query ( see screen shot )
on line 2 there are 5 transactions how can i get them displayed in the query output?
is ther a way to use the power query tool in Excel or power BI to get the data directly in excel or power BI ?
thanks in advance
roger

[Ealfardan]

Hi @Roger_Van_Decraen,

@Roger_Van_Decraen

is ther a way to use the power query tool in Excel or power BI to get the data directly in excel or power BI ?

Absolutely, you can use this connector:

API 2 using Power Query

This is a basic connector to connect Excel (or PowerBI) to Manager api2. let _ExpandCols = (TableToExpand as table, optional ColumnName as text) => let ListAllColumns = Table.ColumnNames(TableToExpand), ColumnsTotal = Table.ColumnCount(TableToExpand), CurrentColumnIndex = if (ColumnName = null) then 0 else List.PositionOf(ListAllColumns, ColumnName), CurrentColumnName = ListAllColumns{CurrentColumnIndex}, CurrentColumnContent = Table.Column(TableToEx…

And navigate to path summary-transactions

[Roger_Van_Decraen]

I succeeded in query the results from the receipts but the result is not what I expected,if you see the screen shot for the account number i would expect the correct account number and not the code displayed, same goes for the projeect number
any help is greatly appreciated
kind regards

Roger
new to manager

[Ealfardan]

So, you have retrieved the object by setting Expand Object. This will give you the raw object data.

If you just want the display data, you can set Expand Object to false and instead pass a list of fields to Field List parameter

[Roger_Van_Decraen]

I nog get the amount number and name but it repeats all accounts over all the lines
I’m not sure what you mean with your last remark " pass a list of fields …)
the query is very slow ( > 5 min for 1600 records )

[Ealfardan]

@Roger_Van_Decraen

I nog get the amount number and name but it repeats all accounts over all the lines

That’s because of Expand Object being set to true. This is normal since this merges the parent record (Receipts) with its lines (Expanded Objects). You can format this output by pivoting.

@Roger_Van_Decraen

the query is very slow ( > 5 min for 1600 records )

That’s also because of Expand Object being set to true. The query gets the tab containing your 1600 records – pretty quickly, then it runs 1600 queries to expand each and every record. These additional 1600 queries are the cause of your slow down.

If you don’t want this, just set Expand Object to false

@Roger_Van_Decraen

I’m not sure what you mean with your last remark " pass a list of fields …)

I was talking about this:

See the last parameter labeled “Field List (optional)”. This can be used to pick and choose specific fields.

[Ealfardan]

The Field List accepts text type list ofields separated by a coma.

For example, if you only want your query to get reference and date, your Field List should be:

Reference,Date

[Roger_Van_Decraen]

thanks for our answers but I did’nt succeed in making it work properly
it return always the alfa numeric code.
So I give up

[Ealfardan]

@Roger_Van_Decraen

it return always the alfa numeric code.

Have you tried setting Expand Object to false?

[Roger_Van_Decraen]

Yes, I did

[Ealfardan]

Have you deleted the previous query where you set Expand Object to true?

I say this because everytime you invoke a Power Query function, it creates a new Query and leaves previous queries unchanged.

Could you please delete all invoked queries and start fresh?

[Roger_Van_Decraen]

I started from scratch again,
as you can see I have all the info from the receipt booking, except the 4 row has the wrong account nr should be 700050 sponsoring,

[Roger_Van_Decraen]

Is there a folow up for this topic or does it end here?

[Ealfardan]

I cannot reproduce this from my end, could you please share the full code from your Advanced Editor?

[Roger_Van_Decraen]

thanks for looking at the code:

let
    Source = Query1("http://127.0.0.1:55667/api2", "key", "receipts", false, null, null),
    #"Filtered Rows1" = Table.SelectRows(Source, each Text.StartsWith([Receipt.Date], "2025")),
    #"Expanded Receipt.Lines" = Table.ExpandListColumn(#"Filtered Rows1", "Receipt.Lines"),
    #"Expanded Receipt.Lines2" = Table.ExpandRecordColumn(#"Expanded Receipt.Lines", "Receipt.Lines", {"Account", "LineDescription", "CustomFields2", "Amount", "Project"}, {"Receipt.Lines.Account", "Receipt.Lines.LineDescription", "Receipt.Lines.CustomFields2", "Receipt.Lines.Amount", "Receipt.Lines.Project"}),
    #"Expanded Receipt.Lines.CustomFields2" = Table.ExpandRecordColumn(#"Expanded Receipt.Lines2", "Receipt.Lines.CustomFields2", {"Strings"}, {"Receipt.Lines.CustomFields2.Strings"}),
    #"Expanded Receipt.Lines.CustomFields2.Strings" = Table.ExpandRecordColumn(#"Expanded Receipt.Lines.CustomFields2", "Receipt.Lines.CustomFields2.Strings", {"a8151f43-de97-449c-994c-4e622b5d175b", "fecfce50-1143-444d-a8b1-2c6e5cc99957"}, {"Receipt.Lines.CustomFields2.Strings.a8151f43-de97-449c-994c-4e622b5d175b", "Receipt.Lines.CustomFields2.Strings.fecfce50-1143-444d-a8b1-2c6e5cc99957"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Receipt.Lines.CustomFields2.Strings",{{"date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Receipt.Lines.Project", "Receipt.HasLineDescription", "Receipt.CustomFields2.Strings", "Receipt.Key", "key", "image", "attachment", "edit", "view", "cleared", "reference", "receivedIn.key", "receivedIn.name", "description", "amount.value", "amount.currency", "Receipt.Date", "Receipt.ReceivedIn", "Receipt.Lines.Account"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Receipt.Lines.CustomFields2.Strings.a8151f43-de97-449c-994c-4e622b5d175b", "Uittreksel nr"}, {"Receipt.Lines.CustomFields2.Strings.fecfce50-1143-444d-a8b1-2c6e5cc99957", "Transacties nr"}, {"Receipt.Lines.Amount", "Amount"}})
in
    #"Renamed Columns"

[Ealfardan]

Hi @Roger_Van_Decraen,

Sorry for the delayed response, I’ve been testing some things out and updating the connector to workaround some PQ hiccups.

Please check the updated connector:

API 2 using Power Query

I have updated the _ExpandCols functions to workaround a PQ hiccup where PQ doesn’t retrieve the full list of fields and instead uses the lazy method of looking into the first record field–which may or may not be complete. To help get the full list, a new function _GetFieldNames was added.

Now back to your query output …

That’s a lot of merges done, which normally result in duplicates from the parent table. This isn’t a problem most of the time, but it makes summary columns such as accounts meaningless.

You will have to remove the accounts field and instead rely on the Receipt.Lines.Account field from the expansion. Any duplicate fields can be pivoted out.

Though I don’t see you using any Custom Fields in your query, which got me thinking, why not instead of querying Receipts and Expanding the objects–which could be costly in terms of performance, why not just query Receipt-lines, you don’t need to expand any objects here?

This is an example

And this is the output:

[Roger_Van_Decraen]

Ealfardan

Thanks for spending your time on the problem

I used the new API2 see screenshot

Are the fields in the field list capitalized, does this matter?

The output from the query with two missing field ( Transactienr & Uittrekselnr) for uittreksel 20

The fields I created in Manager

And the input for the receipt-lines where you can see Uittrekselnr & Transactienr

The description is not giving the correct info

Where can I find the correct names for the fields?

Thanks in advance

roger

Met vriendelijke groeten

Roger

[Roger_Van_Decraen]

Ealfardan

The yellow marked field are missing when I use the receipt-lines in the API2 route. Even If I type them in the fields list they do not appear in the result query

Met vriendelijke groeten

Roger

[Ealfardan]

@Roger_Van_Decraen

The description is not giving the correct info

That’s because it’s now the LineDescription since you are querying the lines, the top-level Description field is not exposed here.

The fields you highlighted in yellow are now called:

• Receipt.Date → Date
• Contact → Customer or Supplier
• Receipt.Lines.LineDescription → Description
• Receipt.Lines.Amount → Amount

Unfortunately and unlike what I previously assumed, you do rely on Custom Fields which will require you to query Receipts and expand the object – which is slow and requires some knowledge of data science to be able to shape the result to your liking.

However, there could be another option, but this requires questioning the purpose of this whole exercise.

The way this looks is that you are not trying to get a report but rather get a complete raw data export of everything. Is that right? If not then please explain your use case for this query because there could be a much simpler solution that we missed.

[Roger_Van_Decraen]

If it is possible to export the raw data then I prefer this option. With power query I can easlily tranform the data and import in power BI for reporting purposes.
thanks for all the effort.
Roger