Forum Discussion
combine 2 nested tables within separate columns
I have a list "5 to rank" which I also converted to a table "TableFFromlisttest".
I would like to achieve the TableFFromlisttest [Table] to have an extra column which i can then perform a Group by List Max or similar to final show only the largest amount with corresponding Column1value.
The "levytable" [Table] is actually refering to the "Levy" table highlighted in yellow which is another query.
What m code can I use so firstly this sub step is achieved
I've tried to use Table.Combine
= Table.AddColumn(#"Removed Other Columns", "Custom", each Table.Combine(([TableFFromlisttest]),([levytable])))
but it doesn't work
Expression.Error: We cannot convert a value of type Table to type List.
Details:
Value=[Table]
Type=[Type]
Any assitance would be appreciated
6 Replies
NewStep=Table.CombineColumns(PreviousStepName,{"TableFFromlisttest","leveytable"},each Table.AddColumn(_{0},"Levy $",each _{1}{[WordinginRegs=[Column1]]}?[#"Levy $"]?),"TableFFromlisttest")
I've worked out how to get the Max from the table
= Table.AddColumn(#"Added Custom12", "Custom", each Table.Max([Custom.1],"Levy $"))giving a record
which when expanded gives the result I'm after
Then only issue remaining is how to point to the List within the "5 to rank" column instead of my 'test list' called "MyList" which sits externally to the table i'm in
I've managed to get most of the solution myself using
#"Added Custom12" = Table.AddColumn(#"Removed Columns", "Custom.1", each Table.SelectRows([levytable], each List.Contains(MyList,[WordinginRegs]))), #"Added Custom6" = Table.AddColumn(#"Added Custom12", "Custom", each Table.Max([Custom.1],"Levy $")), #"Expanded Custom1" = Table.ExpandRecordColumn(#"Added Custom6", "Custom", {"WordinginRegs"}, {"WordinginRegs"}),except for refering to the 'static list 'MyList' instead of the List within the column [5 to rank]
Does anyone have any ideas?
