Forum Discussion

Hashiru's avatar
Hashiru
Icon for Helper I rankHelper I
6 years ago
Solved

Convert a Column of Tables (After Merge Query Step) to a Column of list

Hi everyone,

 

I am trying to convert a column of tables on each row to a column of list on each row.

 

 

 

I will like to convert Time & Labor Column to List or add another column and convert the content each row in column Time & Labor to List. The altemate objective is to sum a numeric column from the table but values related from the many side after the merge

 

Thanks in advance.

Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    Hello Hashiru ,

    were you able to solve the problem with any reply given?

    If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    All the best

    Jimmy

9 Replies

  • Jimmy801's avatar
    Jimmy801
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hello Hashiru 

     

    you can use the function Table.ToRows that creates a simple nested list for every row. However, I would normaly prefer to use Table.ToRecords to get a list of records (contains column header) instead of list of lists (you will loose any header information)

    Here an example that shows Table.ToRows

    let
    Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSgeJYnWilJCALgkG8ZCALgmNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ColumnA = _t, ColumnB = _t, ColumnC = _t]),
    ChangedType = Table.TransformColumnTypes(Quelle,{{"ColumnA", type text}, {"ColumnB", type text}, {"ColumnC", type text}}),
    ToRows = Table.ToRows(ChangedType)
in
    ToRows

     

    Copy paste this code to the advanced editor to see how the solution works

    If this post helps or solves your problem, please mark it as solution.
    Kudos are nice to - thanks
    Have fun

    Jimmy

    • Hashiru's avatar
      Hashiru
      Icon for Helper I rankHelper I
      6 years ago

      Hi Jimmy801,

       

      Thanks for the response, I will like to really sum the values in the column OU Num for where the codes in TRC Status column are WR, WO and WD. The total should be added to new column and included in all rows including the excluded criterium MC

       

       

      Thanks in advance and Merry Christmas

      • Jimmy801's avatar
        Jimmy801
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hello Hashiru 

         

        don't get the connection with your initial request 🙂

        However... `here a few questions

        - is the other data in the table needed? Or do you need only to get the sum of if

        - in case you need the whole table, where do you want to have the sum? In a new column in your main table?

         

        Merry christmas also to you

         

        Jimmy