Create new table by filtering column value

This is the "M" code for the main table:

let
    Source = Csv.Document(File.Contents("C:\temp\powerbi\departments.csv"),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Department", Int64.Type}, {"Name", type text}, {"DeptType", type text}})
in
    #"Changed Type"

For DeptType A

let
    Source = Csv.Document(File.Contents("C:\temp\powerbi\departments.csv"),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Department", Int64.Type}, {"Name", type text}, {"DeptType", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([DeptType] = "A"))
in
    #"Filtered Rows"

For DeptType B

let
    Source = Csv.Document(File.Contents("C:\temp\powerbi\departments.csv"),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Department", Int64.Type}, {"Name", type text}, {"DeptType", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([DeptType] = "B"))
in
    #"Filtered Rows"

Red text indicates the only real difference in the queries. You can filter a column by clicking on the down arrow in the column in the Query Editor and just selecting the value(s) that you want.

Again, I'm not entirely clear on the use case. If you are new to Power BI, you will find that many of the things that you might typically create a star schema for in traditional multi-dimensional cubes are not entirely necessary in Power BI due to how slicers work, etc. So, if you want a particular measure or calculation to just be on DeptType A or DeptType B, you can put that column in a visual along with your measure and the context of the visual will give you the right answer. Just throwing that out there because it is something I had to slowly realize over time coming from a more traditional BI background.

Hi Anonymous,

Smoupre’s solution seems well, I’d like to share other way to solve your issue based on dax:

SubTable A = CALCULATETABLE(“Main Table”,FILTER(“Main Table”, “Main Table”[DeptType]="A"))

SubTable B = CALCULATETABLE(“Main Table”,FILTER(“Main Table”, “Main Table”[DeptType]="B"))

Regards,

Xiaoxin Sheng