hi Folks
Hope you are well. I'm attaching a file. I'm trying to do a fill down/or fill up on the MajorCap and MinorCap headings in it. I've tried Find and Replace Values, I've tried Error replace. It looks as though there is something in the blank cells that I can't see that's stopping the fill. I did do a CountA on the raw Excel version and there is definitely something there...grrrr.
Secondly the dates is presented as 201401 format. I want to make a pivot of this data so I want to split it into year and then convert the 01 bit into January. I can split it fine...and have tried playing with the month M function using a custom function (alas, it seems to be working but has a hissy fit when I try to run it...) . There's 30K odd rows in the data so this would save a lot of time if I could get it up and running in PQ so I'm only sending in a sample..
Thank you very much for your help. 🙂
Hi Anne,
Those "blanks" should be replaced with null, in order to fill up or down. They usually come from empty strings created by a formula like "=IF(1,"",2)
They are called "zero length strings", but some functions are not seeing these as blanks, as you noticed already (COUNTA is one of them)
The trick is to type nothing in Value to Find field, leave it empty, then type null in Replace with field.
Now you will be able to fill up or down.
To convert a text to date, add a new column and use this simple formula:
= Date.FromText(Number.ToText([Month])&"01")
Let us know if you managed to make it work 🙂
Catalin
hi Catalin
Sigh...never works out like you think. I tried that for both columns. Still no luck. I even tried find and replace with empty field and then zero and then run it again with zero and null. Fill up and fill down still not working...
I split the column and now have year in one column. The second column called Month.2 (which is the one I wanted the formula on). I tried your formula above but it identified the &”01″ as an invalid identifier...what is the &”01″ supposed to do in this scenario?
Just checking but presume the Month in the formula above refers to the field name not that it's a month....or how do you reference the field name in it?
Any other ideas?
Thanks for you help 🙂
Anne
looks like the cell is not blank, you have a space there. Try to type a space in Value to Find field, and "null" in the Replace wiith field. Expand the advanced options, and check the option to match the entire cell content, otherwise it will replace the spaces with null in all your text values.
The [Month] column is the last column in your data, with "201401" style values.
Cheers,
Catalin
I did get it working but what I did was that in the raw Excel data, I cleaned out the blank cells using Find & Replace (two steps) - used weird characters for my Find & Replace. Then I did the Fill Up/Down in PQ and that worked..so that is sorted 🙂
Now how can I split my month column so that I have 2014 in one column for the year - I know how to do that. But what formula do I need to do so that instead of 1 I get January? I've had a look at the various PQ formulas but can't find one to fit. I know I can do it in Excel with the text formula but how to do it in PQ? Thanks Anne
Hi Anne,
The Replacement will work in power query too, not only in the source data, you can do it as a step in your query.
You need to transform the 201401 column to a date, before converting the date to display as a month name.
You can add a new column, with the formula already provided:
= Date.FromText(Number.ToText([Month])&”01″)
Then you have to add a new column, and add a formula that will format the date as "MMMM", just like excel TEXT formula does. The equivalent of excel TEXT formula is Date.ToText in power query:
Date.ToText([Custom],"MMMM")
The Custom column is the one created on the previous step, which converts the Month column to a date, by adding a suffix.
There is another way (usually there are more ways to achieve the same result 🙂 )
You can convert the Month column to a date without adding a custom column, using transformation tools:
Select the Month column, go to Transform tab in ribbon and from Text Column section, use Format-Add Suffix command, and add "01" to the entire column.
Now, if you simply change the type of this column to Date type, it will work (power query will not identify 201401 as a date, that's why you need to add that "01' suffix before changing to Date type)
The last step is identical to the one from the previous method:
Date.ToText([Month],"MMMM")
The only difference is that in the first method, we use 2 added columns, in the second method we convert the existing column to date, and we add only one column with Date.Totext formula.
The query should be similar to this:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Suffix" = Table.TransformColumns(Source, {{"Month", each Text.From(_, "en-AU") & "01", type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Added Suffix",{{"Month", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.ToText([Month],"MMMM")),
#"Replaced Value" = Table.ReplaceValue(#"Added Custom"," ",null,Replacer.ReplaceValue,{"MINORCAP"}),
#"Filled Up" = Table.FillUp(#"Replaced Value",{"MINORCAP"})
in
#"Filled Up"
Cheers,
Catalin
hi Catalin
NOW I understand why you added the 01 - thanks for that explanation. I got all that working. I just added the suffix, converted to date and that turned out to be enough. I could then create a pivot on it.
THANK YOU 🙂
But it's really weird. I did exactly the same clean up task in Excel and PQ. Those steps worked in Excel but NOT in PQ so I did end up cleaning up the data in Excel and THEN when I put it into PQ - the fill up and down worked perfectly. That's strange isn't it?
Thanks for your help. I really appreciate it 🙂
You're wellcome Anne 🙂
No idea why the replacement does not work, as you can see in the query I did the replacement and it works (replacing a space with the text "null").
More, if you use the query in the file you provided, it should work.
Glad to hear you got it working 🙂
Cheers,
Catalin
