How to divide one table to several table by a field in Power query?

[yanzhao_LBS]

I have a table like this,and I want to make it divide to several(in this example is 4,as I want to divide it by field month )

[Ahmedx · Accepted solution]

pls try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Rc09DsAgCIbhuzA7FJQfx7bHMN7/GgVi6PANPsmLa8ENDdDH1hl2W/AcQGFKeA+YaU+IhHzKMitJILsqCUCiKnoWQ6sIQJ1cRYB0+T8ZvkkVxNOGWgUJU/3C/gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Month = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Month", Int64.Type}, {"Value", Int64.Type}}),
    Custom1 = Table.Split( #"Changed Type",3)
in
    Custom1

[yanzhao_LBS]

Thanks for your reply. 

I tried your code and it did works. And can you tell me is there a button on tool bar for this "Custom1" or it can only work by coding?

and my initial requirement is first divide the table and do some job on each of them and then at last I still need to combine them into 1 table, current I copy several of the table and filter the month for each of them and use Append  to combine them to one . And if I  use your approach ,do you have a solution also on how to combine them then?

[Ahmedx]

pls try

[Anonymous]

Hi @yanzhao_LBS ,

Everyone's insights are great.

In addititon, you can try the reference feature and it will be a little easier.

In the query panel on the left, right-click on your original table, then right-click on it again and select "Reference". This will create a new query that references the first. Apply your year filter to the new query, rename the query and repeat as needed.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I think the question is pretty clear, and the reasons for needing this solution are many.

Don't bother copying tables--you could have a thousand tables to copy. After promoting the headers, just use the built-in function Table.Split(TableName, 3) and that's it. The "3" in the parameter is the page size (number of rows) for each resulting table. The result is a list of tables. Turn that list into a table, and you can then add a column using the add column GUI and using = [TableColumnName][Month]{0} so you know which tables contain what months.

--Nate

[PhilipTreacy]

@yanzhao_LBS 

Duplicate that table as many times as needed.  Filter for the values you want to keep (the month)  All other rows will be removed.

Why do you want to do this?  Sometimes people ask for a solution thinking that's the best way to achieve something rather than explaining what it is they need to achieve and then receiving the best solution.

For example, you could load this table into Excel and/or PBI and then filter for the rows you want to display.  No need to create 4 tables.

Regards

Phil

[dharmendars007]

Hello @yanzhao_LBS , 

Could you please share the expected result you are lloking in the table visual and also the logic you are trying to build this will help us to create the same in PowerQuery..

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!

Thank You

Dharmendar S

LinkedIN