Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
In PowerQuery, How can we use Create Table from an existing table by filtering rows?
Thanks
Solved! Go to 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.
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
Thanks David.
But referencing is Duplicating Data. I need to create a new Table by filtering rows.
Tried Table.RemoveRows but giving error
Can you provide some screenshots and examples of your current scenario and the desired output?
Best
David
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 |
@yogeshmaney , have you tried Table.SelectRows()?
= Table.SelectRows(
#"Previous step",
each [Location] = "US"
)
Let us know if that works for you
Best
David
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.
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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
51 | |
19 | |
12 | |
11 |