Hello
Most issues I posted about originally have now been resolved (once I realized my first file was a header row only!). Just need some help with formula language please.
If I were doing this in excel my formula would be =IF($A2="SALES",$B2*-1,$B2). Is there an equivalent type of statement for power query please?
Many thanks
Jan
Hi Jan,
I can't translate the formula above for you as it doesn't refer to column names, which you need for a Power Query IF. However, session 6.12 shows you how to write an IF function in Power Query. If you have any problems please let me know.
Mynda
Hi Mynda
Thanks very much. I had found the right way to write the IF statement and your lesson 6.12 confirmed it was OK (although my first attempt incorrectly included parentheses - old excel habits die hard!). Unfortunately, it's still not working for me.
I made sure my data format for the Transaction Qty column was whole numbers, but it is still not delivering a result in the custom column (it is only returning numbers for all rows that aren't SALE type). See screen snips.
There seems to be a bigger problem though in that I have inadvertently included files that I didn't want to. The folder I am retrieving files from has the .csv files I want, but also 2 folders with unwanted files (I had "hidden" files I didn't want to import into other folders). It looks like the query is retrieving files from these folders and this is causing issues as they are not in the same format (different column arrangement) as the files I wanted to combine. Is there a way around this other than to move the sub-folders out? In case it helps make sense of this query, the file path where my .csv files are is ...Remote Ledger ImportsWWFBRETTSIMPORTED (new files get put here automatically after they've been imported into my ERP system) and the folders as per the following path ...Remote Ledger ImportsWWFBRETTSIMPORTEDpre_fix and ..Remote Ledger ImportsWWFBRETTSIMPORTEDRoadrunner. I assumed files would be ignored that were in the sub-folders, but they're not ..?
Many thansk
Jan
Hi Jan,
It looks like your Transaction Quantity column (I'm guessing it's the second column because there are no column headers on the screenshot), does not have a numeric data type because the numbers are left aligned, which suggests it's text. Insert a step before the IF formula to fix the data type so they are numbers, then see if that fixes your formula.
In regards to your import issues, you can try adding a filter after the source step to exclude the folders you don't want.
Mynda
That cracked it - many thanks!! ...I must remember the order in which steps are applied is important! 🙂
