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

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}})