CreateTable in Power Query

[yogeshmaney]

Hello,

In PowerQuery, How can we use Create Table from an existing table by filtering rows? 

Thanks

[Geradav]

Hi @yogeshmaney 

Not sure to clearly understand your question but I am thinking you could Reference an existing query and apply the desired filters.

Inside Power Query Editor, right-click a query in the Queries pane (left side) and click "Reference".

It will create a new query (new table) dependant on the first one. Then you apply your filters as desired.

Let us know if that works for you.

Best

David

[yogeshmaney]

Thanks David. 

But referencing is Duplicating Data. I need to create a new Table by filtering rows.

Tried Table.RemoveRows but giving error

[Geradav]

@yogeshmaney 

Can you provide some screenshots and examples of your current scenario and the desired output?

Best

David

[yogeshmaney]

Here's the sample

I have

Sr	Item	Location
1	A	US
2	B	UK
3	C	US
4	D	FR
5	E	US

I want, A table for US  location 

Sr	Item	Location
1	A	US
3	C	US
5	E	US

[Geradav]

@yogeshmaney , have you tried Table.SelectRows()?

= Table.SelectRows(
  #"Previous step",
   each [Location] = "US"
)

Let us know if that works for you

Best

David 

[yogeshmaney · Accepted solution]

Hi

I referenced DataSet &

Tried Following

let
Source = Data1,
#"Added Custom" = Table.AddColumn(Source, "Index", each if [Location] = "US" then "" else 0/1),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Index", type binary}}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type", {"Index"}),
#"Removed Columns" = Table.RemoveColumns(#"Removed Errors",{"Index"})
in
#"Removed Columns"

Its Working for me. 

[Geradav]

Great! Glad you found something that works for you @yogeshmaney 

I guess you could simplify that M code with:

let
Source = = Table.SelectRows(
   Data1,
   each [Location] = "US"
)
in Source

Best

David 

[yogeshmaney]

@David  That's Filtering Column. Data I am fetching is from Event Store. Each Referencing is around 750 MB . Need 20-25 such reference. And Making File heavy. Inside there are JSON blobs.

Deleting is the only option