Extracting data

[doriannjeshi]

Hello,
I need help with extracting data from a large sheet based on table 1 to table 2 result

The length of the nr of rows to be copied down is determined by the column 3 which is always containing 0 .

TABLE1 data				TABLE2 result
COL1	COL2	COL3		COL1	COL2	COL3
aaa	bbb	vvvw		aaa	bbb	vvvw
12325		000		aaa	bbb	vvvw
6494		000		aaa	bbb	vvvw
4232		000		aaa	bbb	vvvw
2965		000		aaa	bbb	vvvw
abd	cdc	wew		abd	cdc	wew
abd		000		abd	cdc	wew
abd		000		abd	cdc	wew
abd		000		abd	cdc	wew
a34	223	113		a34	223	113
23		000		a34	223	113
2		000		a34	223	113

 

[Kerryx]

Power query can help

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	TABLE1 data				TABLE2 result
2	COL1	COL2	COL3		COL1	COL2	COL3		COL1	COL2	COL3
3									aaa	bbb	vvvw
4	aaa	bbb	vvvw		aaa	bbb	vvvw		aaa	bbb	vvvw
5	12325		0		aaa	bbb	vvvw		aaa	bbb	vvvw
6	6494		0		aaa	bbb	vvvw		aaa	bbb	vvvw
7	4232		0		aaa	bbb	vvvw		aaa	bbb	vvvw
8	2965		0		aaa	bbb	vvvw		abd	cdc	wew
9	abd	cdc	wew		abd	cdc	wew		abd	cdc	wew
10	abd		0		abd	cdc	wew		abd	cdc	wew
11	abd		0		abd	cdc	wew		abd	cdc	wew
12	abd		0		abd	cdc	wew		a34	223	113
13	a34	223	113		a34	223	113		a34	223	113
14	23		0		a34	223	113		a34	223	113
15	2		0		a34	223	113
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"COL1", type any}, {"COL2", type any}, {"COL3", type any}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"Filtered Rows" = Table.SelectRows(#"Removed Top Rows", each true),
    #"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "Custom", each if [COL3] = 0 then null else [COL1]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"COL1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "COL2", "COL3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "COL1"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",0,null,Replacer.ReplaceValue,{"COL3"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"COL1", "COL2", "COL3"})
in
    #"Filled Down"

 

[doriannjeshi]

Hi Kerryx, thank you for the solution !
First time using Powerquery : Editor gives me this error: We couldn't find an Excel table named 'table1'.

 

[Kerryx]

Did you start by doing the following
1- Highlight the range for your source data , below it is "A2:C15"
2- Go to Data tab and click on Get Data -> from Table/ Range

As long as there are no other tables on the sheet it will convert this to Table1

If your sheet already has the source info in an existing table , select the table got to Table Design and find the existing table name , replace the name in the formula below with existing name if there


Here are some good links from @alansidman on how to set up and use PQ

How to use Power Query

 

[doriannjeshi]

I dont know what I am doing wrong sth that got to do with the table
it gives me this error:
the column 'Col1" of the table wasnt found

 

[Kerryx]

Did you tick this box when importing the range, this tells PQ that the top row in your selection are the headers for the columns

so when it is imported you have this

Click on Advanced editor, which will already contain the code as shown below, just delete that code and put in the code i have given you above , click on done, then LOad and close, select where in the excel sheet you want it to go., and Bob's your Aunty

 

[doriannjeshi]

Thank you for the guide Kerryx, It was the headers

now not all data gets converted , the data below COL1 , COL3 dont get filled down

 

[Kerryx]

Based on the codes these are the steps applied on the right of your screen, once you click on any step it will show you what has been done as you see in COL 3 , i had just 0 in some rows but reviewing your screenshots i see "000" so the step where the Additional column was added was not doing anything as it did not find 0 because it should have been looking for "000"
So just replace this line

#"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "Custom", each if [COL3] = "000" then null else [COL1]),

 

[Kerryx]

Here is the full code with the replaced line and also had to change the replace value in COL3 as my original code had find 0 instead of find "000"
to replace with null, hopefully that should sort it all, i am assuming "000" is text format

let
 let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"COL1", type any}, {"COL2", type any}, {"COL3", type any}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"Filtered Rows" = Table.SelectRows(#"Removed Top Rows", each true),
    #"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "Custom", each if [COL3] = "000" then null else [COL1]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"COL1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "COL2", "COL3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "COL1"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","000",null,Replacer.ReplaceValue,{"COL3"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"COL1", "COL2", "COL3"})
in
    #"Filled Down"
"