Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
Please let me know how to create new table from one Big table. I would like to create 3-4 small tables from one big based on one the column value.
Thanks
Harish
Solved! Go to Solution.
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 @harishkanyal,
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |