PQ - table update with conditional on cell

[Emmanuel]

Dear all
Below is on extraxt of a wider table with many lines and columns. I'id like to add columns showing only the account number (eg 1013000 etc) or account name (in CAPs) on the line where a date is inserted, and subsequently to delete all lines with no date.
Can you please help on this one ?
Thanks
Best

	10130000	CAPITAL SOUSCRIT APPEELE VERSE
01/01/17	AN 000	S.A.N.
		Total compte 10130000
	10410000	PRIME D'EMISSION
01/01/17	AN 000	S.A.N.
		Total compte 10410000
	10610000	RESERVE LEGALE
01/01/17	AN 000	S.A.N.
		Total compte 10610000
		Total classe 10
	11000000	REPORT A NOUVEAU
01/01/17	AN 000	S.A.N.
		Total compte 11000000

[Rudi]

Hi,

Here is a quick and dirty way of doing it (on the data sample you posted)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}, {"Column2", Int64.Type}, {"Column3", type text}}),
    #"Filled Up" = Table.FillUp(#"Changed Type",{"Column1"}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Filled Up", {{"Column2", null}}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Errors", each ([Column2] <> null))
in
    #"Filtered Rows"

[Emmanuel]

Hi Rudi,
Thank you so much. It appears that there is an error message "Table1" not recognised. I tried changing the name with the actual name of the tabl on my Excel spreadsheet, but still does not work... May be an easy trick?

[Rudi]

In order to create a solution I copied your sample data into Excel and started PQ with "From Table" --which created Table 1 in my reply.

You probably will not have "Table 1" as a source so you need to replicate the steps I posted on your source.

If you are familiar with the Advanced Code window, you can copy and paste the last 4 steps under your source step:

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}, {"Column2", Int64.Type}, {"Column3", type text}}),
    #"Filled Up" = Table.FillUp(#"Changed Type",{"Column1"}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Filled Up", {{"Column2", null}}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Errors", each ([Column2] <> null))

If you are still stuck, I will upload a sample file...
Let me know.

[Emmanuel]

I realise I must miss a very easy thing, but for some reasons, the best I could get is a table in a new column.
Attached is a sample of data
sorry for the hassle and thank you for your kind help

[Rudi]

I followed the same steps on your table based on the sample query I posted earlier.
I how the result is what you intended to get?

testPQ.xlsx

[Emmanuel]

Not exactly. The tab Import contains the data to be imported and treated. The tab Result contains the desired results. basically, it means adding columns and insert the numbers and titles (CPAs) for each line that has a date on columnA. The numbers and titles change when appropriate. Hopefully the example on the spreadsheet is clearer than I am

[Rudi]

Here is an updated version. Slowly getting there

I'm not sure where the other column come from but you can guide me through that if you cannot work it from here.

See attached.

[Emmanuel]

Looks awesome!
Thank you so much. I still have some problems to save and reuse this request inserting the full database, but I will play round with this. The other columns are links with a table that I will construct in addition. I need to learn...
Many thanks for you great and much appreciated help
Best