Can't seem to implement Record.FieldValues properly

[rpiboy_1]

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.

[ImkeF · Accepted solution]

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

[rpiboy_1]

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.

[ImkeF]

Hi @rpiboy_1 ,
having problems understanding what you want to achieve here. Could you please post a link to a file with some sample data, giving source data and expected result? 

[rpiboy_1]

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