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 co...
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

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

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

      Hi Jimmy801,

       

      The whole table is needed and I am required to sum two columns. I will like to have the sum on a new column in the sub table, this way I can expand the required columns my analysis needed

       

      Thanks

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

        Hello Hashiru 

         

        I don't know the second colum 🙂

        but to sum the OU Num-Column of the sub-table Time and Labour add a new column (select the symbol in the program) and past in this code. This should do the trick

        List.Sum([#"Time & Labour"][#"OU Num"])

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

        Jimmy

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    You can transform the Time & Labor column with M code to the sum of the OU Num filtered on WR,WO and WD. The code below presumes that the prior step is called Merged Queries (change it as needed). 

     

     

    = Table.TransformColumns(#"Merged Queries",{{"Time & Labor", each List.Sum(Table.SelectRows(_, each List.Contains({"WR","WO","WD"},[TRC Status] ))[OU Num]), type number}})

     

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

      AThanks mcybulski 

       

      I tested your proposed solution which is good but I lost the other columns needed for the solution. I was trying to use Table.AddColumn to add the column with the other columns of the sub tables then expand to the required columns

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Ok then duplicate your column prior to transforming.

         

        DupicateColumn= Table.DuplicateColumn(#"Merged Queries", "Time & Labor", "Time & Labor - Copy"),
Result= Table.TransformColumns( DupicateColumn,{{"Time & Labor", each List.Sum(Table.SelectRows(_, each List.Contains({"WR","WO","WD"},[TRC Status] ))[OU Num]), type number}})