Splitting rows dynamically into tables

[Nofunworking]

Hi all I came across with this tricky raw data and would see if anyone can help me with cleaning this data, I have a spreadsheet of transactions listed with multiple currencies altogether back to back that I want them to be separated:

currency1

transactioninfo1

transactioninfo2

transactioninfo3

transactioninfo4

so on...

currency2

transactioninfo1

...

currency3

transactioninfo1

transactioninfo2

...

and I would like to separate them into queries of currencies so that they are not back to back. I did some research saying that I can use  table.splitat but it seems like I can only split the table into two parts. Is there any way that I can split into multiple table or make a nested table by currencies or anything close?

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solution Omid_Motamedise  and lbendlin  provided, and i want to offer some more infotmation for user to refer to.

hello @Nofunworking , you can refet to the following sample.

Create the following m code to change the table to the the following format.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSi4tKkrNS640VIrViVYqKUrMK05MLsnMz8vMS8vHKmiETdAYm6AJWBBmA1Z9VLUBqxLKbYgFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column1],"currency") then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Custom"}, {{"Data", each Table.SelectColumns(_,{"Column1"}), type table}})
in
    #"Grouped Rows"

Then create a query to set it as a function.

(a as text) =>
let 
  _Filter= Table.SelectRows(Query1,each [Custom]=a)[Data]{0}
in
    _Filter

Then in this function, input the currency, such as 'currency1'

Then it will generate a new table.

You can use this way to split these rows to tables.

Best Regards!

Yolo Zhu

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

[Omid_Motamedise]

You can transform it and provide the data of different currency under each other in one table

[lbendlin]

Power Query cannot write to a dynamic number of tables. All Expressions and Partitions in Power Query are static.

[Nofunworking]

Hey thx for replying, do u have any alternative way to suggest?

[lbendlin]

Use a different tool that supports dynamic table creation, or change your requirement.