Replace specific value with column name in variable number of columns

[Syndicate_Admin]

Starting from a calendar ListObject in Excel which has rows for each date (first column = "Date"), columns for each employee, and a "V" in each cell where an employee is on vacation on that date.

I want to create a transformed list which has the employee name (as specified in the column header) in each cell with a "V". Of course, this is trivial with formulas, but the downside is that I'd have to create a 2nd set of colums for each employee manually. Since employees come and go, I'd rather maintain the necessary columns for each employee in one place, and create a PowerQuery to dynamically create the transformed list with employee names instead of a "V". (I have a good reason for this 🙂 ) Like so:

Now I've worked out how to reference the column name with Table.ColumnNames(#""){}}. But I'm stumped about applying the replace to all columns (skipping Date), and referencing the correct column index for each column.

Here's the code (formatted as C# because it looks best):

let
    Source = Excel.CurrentWorkbook(){[Name="TablePlannedVacations"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Aparupa", type text}, {"Eunice", type text}, {"Floris", type text}, {"Frank", type text}, {"Gijsbert", type text}, {"Jasper", type text}, {"Ken", type text}, {"Mahesh", type text}, {"Mark", type text}, {"Niels", type text}, {"Remco", type text}, {"Rob", type text}, {"Sepideh", type text}, {"Stan", type text}, {"Thijs", type text}}),
    #"Replaced Aparupa" = Table.ReplaceValue(#"Changed Type","V","Aparupa",Replacer.ReplaceValue,{Table.ColumnNames(#"Changed Type"){1}}),
    #"Replaced Eunice" = Table.ReplaceValue(#"Replaced Aparupa","V","Eunice",Replacer.ReplaceValue,{Table.ColumnNames(#"Changed Type"){2}})
in
    #"Replaced Eunice"

So the challenge here is to create a generic statement instead of the #"Replaced Aparupa", #"Replaced Eunice" etc. transformations, that goes through all columns except Date, takes the name from the column name, and replaces "V" with that name. (Also, all non-"V" values need to be cleared, but that's trivial by comparison, is my guess.)

My background is entirely procedural (with a bit of SQL), so my reflex is to search for a For..Next or Do..Loop type solution. But I except PowerQuery has a neater solution. What is it?

[Syndicate_Admin]

Look below

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtR1LE3XNTJW0lECojAwjtWJVjJCiIfB5EDixhjqwcImOJSbYlduhiIMUR0LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Joe = _t, Mary = _t, Achmed = _t]),
    Custom1 = Table.FromRecords(
    Table.TransformRows(Source, (r) => List.Accumulate(Table.ColumnNames(Source), r, (s,c)=> 
        Record.TransformFields(s,{{c, each if _ = "V" then c else _}})))
        , Value.Type(Source))
in
    Custom1

[Syndicate_Admin]

Hi @Vijay_A_Verma, thanks for your response. In order for me to make sense of this, let me check first whether I understand it all. The second statement (Custom1 = ) looks like I might be able to make sense of it. The first statement (Source = ), not so much. But I expect (and hope) is that the first statement is merely an alternative source definition, and the answer to my problem is completely in the second statement, is that right?

[Syndicate_Admin]

Yes, 2nd statement is the answer where Source will be replaced with #"Changed Type". So the code becomes below

let
    Source = Excel.CurrentWorkbook(){[Name="TablePlannedVacations"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Aparupa", type text}, {"Eunice", type text}, {"Floris", type text}, {"Frank", type text}, {"Gijsbert", type text}, {"Jasper", type text}, {"Ken", type text}, {"Mahesh", type text}, {"Mark", type text}, {"Niels", type text}, {"Remco", type text}, {"Rob", type text}, {"Sepideh", type text}, {"Stan", type text}, {"Thijs", type text}}),
Custom1 = Table.FromRecords(
    Table.TransformRows(#"Changed Type", (r) => List.Accumulate(Table.ColumnNames(#"Changed Type"), r, (s,c)=> 
        Record.TransformFields(s,{{c, each if _ = "V" then c else null}})))
        , Value.Type(#"Changed Type"))
in
    Custom1

[Syndicate_Admin]

So let's see if I understand this:

• Table.TransformRows iterates through all records in the previous result set and applies a transformation to each record.
• r is the variable/parameter name for the current record to be transformed.
• (r) => List.Accumulate specifies what transformation needs to be applied to r
• List.Accumulate iterates through the column headers, and through Record.TransformFields replaces any "V" value in the record with the corresponding header.

I must admit that this last bit has me stumped. It works, but I don't understand it. If r indeed represents the record iteration from TransformRows, what do s and c represent? Or more generally speaking: what's happening in that List.Accumulate/Record.TransformFields construct?

Also, is there a way to skip the Date column? I need to retain the data in that.

[Syndicate_Admin]

Hi,

Unpivot, Replace Value, Pivot

let
Source = Your_Source,
Unpivot = Table.UnpivotOtherColumns(Source, {"Date"}, "Attribute", "Value"),
Replace_Value = Table.ReplaceValue(Unpivot,"V",each [Attribute],Replacer.ReplaceText,{"Value"}),
Pivot = Table.Pivot(Replace_Value, List.Distinct(Replace_Value[Attribute]), "Attribute", "Value")
in
Pivot

Stéphane 

[Syndicate_Admin]

Hi @slorin , thanks for your quick response. I'm afraid though that as a relative noob, I'm not quite clear how this works. Could you provide some explanation with the code?

[Syndicate_Admin]

Bonjour
Je vais répondre en français car mon anglais n'est pas assez bon (vous demanderez à votre navigateur de traduire !)

Une bonne pratique consiste à transformer vos données qui ont les noms en colonne en les dépivotant (unpivot) afin d'avoir une colonne avec les dates et une colonne avec les noms.

Dans votre cas, il suffit ensuite de remplacer les V par les noms (la colonne Attribute contient les noms, la colonne Value uniquement la lettre V).

Enfin, comme vous souhaitez avoir les noms en colonne, il faut pivoter afin de retrouver la disposition initiale.

Stéphane

[Syndicate_Admin]

This works, in the sense that it does what I asked, so I'm accepting it as a solution. Since I don't actually understand the workings of it, I prefer the other solution, which also gives me more room to do additional replacements while unpivoted.

[Syndicate_Admin]

Parfait, merci!