Forum Discussion

AUDISU's avatar
AUDISU
Icon for Resolver III rankResolver III
4 years ago

Filter Reversal Entries - Xero

Hi,   I load data to PowerBi from Xero and I use JournalsAccrual end point. It shows me both live and reversal entries. Have any idea how to filter(remove) reversal entries?   Thank you.
Need Help
Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

Let's assume column name is Amount and reversal entries are shown with minus sign. Then you can insert following step in formula bar to filter out (remove) reversal entries where #"Changed Type" should be replaced with your previous step

= Table.SelectRows(#"Changed Type", each [Amount] < 0)

 See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI2UYrViVYyAjKNjMFMkKiuEYJpAVega4pQawRnmpohKUAYYWigFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer ID" = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer ID", Int64.Type}, {"Amount", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Amount] < 0)
in
    #"Filtered Rows"