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"