Forum Discussion
Can't seem to implement Record.FieldValues properly
I still keep having issues on how to implement Record.FieldValues in given situations. Here is the M code I have currently.
let
ColumnFilter = "Status",
Source = Table1,
#"Headers" = Table.ColumnNames(Source),
#"Converted to Table" = Table.FromList(Headers, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Conditional Column" = Table.AddColumn(#"Converted to Table", "Custom", each if Text.Contains([Column1], ColumnFilter) then [Column1] else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Column1"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Custom] <> null)),
#"HeaderList" = #"Filtered Rows"[Custom],
#"Merged Columns" = Table.AddColumn(Source, "Merged", each Text.Combine(#"HeaderList", ""), type text)
in
#"Merged Columns"
The problem I'm having is my #"Merged Columns" statement is literally merging the Column Header Values, for all rows, as opposed to merging the row values of the columns defined by the #"HeaderList" statement. I know I need to use Record.FieldValues in some form, or some Record.x function, but I'm not having any luck. Any pointers, and explanation would be much appreciated.
Hi rpiboy_1 ,
thanks, that's helpful.
You would have to adjust like this:let ColumnFilter = "Status", Source = Table1, #"Headers" = Table.ColumnNames(Source), #"Converted to Table" = Table.FromList(Headers, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Added Conditional Column" = Table.AddColumn(#"Converted to Table", "Custom", each if Text.Contains([Column1], ColumnFilter) then [Column1] else null), #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Column1"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Custom] <> null)), #"HeaderList" = #"Filtered Rows"[Custom], #"Merged Columns" = Table.AddColumn(Source, "Merged", each Text.Combine(Record.FieldValues(Record.SelectFields(_,#"HeaderList")), ""), type text) in #"Merged Columns"so first you select the relevant fields of the records and then you fetch their values afterwards:
Record.FieldValues(Record.SelectFields(_,#"HeaderList"))
4 Replies
Hi rpiboy_1 ,
thanks, that's helpful.
You would have to adjust like this:let ColumnFilter = "Status", Source = Table1, #"Headers" = Table.ColumnNames(Source), #"Converted to Table" = Table.FromList(Headers, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Added Conditional Column" = Table.AddColumn(#"Converted to Table", "Custom", each if Text.Contains([Column1], ColumnFilter) then [Column1] else null), #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Column1"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Custom] <> null)), #"HeaderList" = #"Filtered Rows"[Custom], #"Merged Columns" = Table.AddColumn(Source, "Merged", each Text.Combine(Record.FieldValues(Record.SelectFields(_,#"HeaderList")), ""), type text) in #"Merged Columns"so first you select the relevant fields of the records and then you fetch their values afterwards:
Record.FieldValues(Record.SelectFields(_,#"HeaderList"))Perfect, thanks so much, and the explanation makes sense. Seems like I was generally moving in the right direction, just didn't realize the need to apply the Record.SelectFields function.
Let's say I have a table like this:
Col1 Col2 StatusCol1 StatusCol2 StatusCol3 <value> <value> A BB CCC <value> <value> Z XX YYY <value> <value> CCC BB A I want to merge all columns with 'status' in the name as a new column, so I get this:
Col1 Col2 StatusCol1 StatusCol2 StatusCol3 Merged <value> <value> A BB CCC ABBCCC <value> <value> Z XX YYY ZXXYYY <value> <value> CCC BB A CCCBBA
Right now, I'm getting:
Col1 Col2 StatusCol1 StatusCol2 StatusCol3 Merged <value> <value> A BB CCC StatusCol1StatusCol2StatusCol3 <value> <value> Z XX YYY StatusCol1StatusCol2StatusCol3 <value> <value> CCC BB A StatusCol1StatusCol2StatusCol3
