Forum Discussion

Nofunworking's avatar
Nofunworking
New Member
1 year ago
Solved

Splitting rows dynamically into tables

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?

 

 

 

 

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

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

    • Nofunworking's avatar
      Nofunworking
      New Member
      1 year ago

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

      • lbendlin's avatar
        lbendlin
        Super User
        1 year ago

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

  • Omid_Motamedise's avatar
    Omid_Motamedise
    Super User
    1 year ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.