The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
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"