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

[Hashiru]

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.

[Jimmy801]

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]

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

[Anonymous]

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]

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]

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

[Jimmy801]

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]

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 · Accepted solution]

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]

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