I am new to Power Pivot and busy teaching myself
I have tried to set up a formula on Sheet4 to compute the Net Value in Col G (it is the value in Col E-Col F eg E2-F2)
it would be appreciated if someone could kindly assist me
I am new to Power Pivot and busy teaching myself
I have tried to set up a formula on Sheet4 to compute the Net Value in Col G (it is the value in Col E-Col F eg E2-F2)
it would be appreciated if someone could kindly assist me
I have also posted on link below
https://www.mrexcel.com/forum/power-...ml#post5256460
Hi Howard,
I looked at your workbook earlier today and found that your debit and credit columns contain Text and not Values. I tried to convert them to values using an M Language command of "Values.FromText" without success. I couldn't figure why they didn't convert. I also tried to Clean those two columns before trying to convert them to values without success. The links to the real data is missing so I was working on the data in the sheet itself. I think to get further in question, I'd need the real data you are trying to import using PQ.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Hi Marvin
Thanks for your reply and input
attached please find source files
Hi Howard,
I simply can't get your Debit and Credit columns to convert to values. I've tried M language Values.FromText and the default convert to currency with no success. I do find your dates are a little screwy as the clothing seems to have a different format than shirts. M/D/Y is confused with D/M/Y and I've corrected the dates.
Maybe someone else can jump in and find why those things that look like numbers in the debit and credit column don't convert to numbers.
Pivot Pivot Formula.xlsx
Thanks for trying Marvin
I have checked the source file and If I subtract Col E from Col F eg E2-F2 , It get the correct result, but not when I import the files into Power pivot
Copying an empty cell, then using PasteSpecial > Add works.
Hi Jason
I tried your suggestion and it worked
Thanks for your input
Hi Marvin
Thanks once again for trying to resolve the issue, which Jason kindly helped to resolve
Sometimes it is such simple thing that causes a problem
FWIW, Power Query can convert the columns to currency without any issues.
Rory
@ rorya, Given that MarvinP was unable to convert the columns with PQ, do you not think it would be helpful to all if you provided an explanation of how it can be done instead of just saying that it can?
Just select the column, and change the type dropdown to Currency.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks