Concatenate row values to form header

[cgkas]

Hello to all,

I have the folowing table.

CustomerID	Name.1	Head2	Head4	Head5
		Sub Head2	Sub Head4	Sub Head5
		Sub Head2-1	Sub Head4-1	Sub Head5-1
		Sub Head2-2	Sub Head4-2	Sub Head5-2
		Phone	State	Country
48	Ana	2100-98	K	Y
9	George	93-993	T	P

I'd like to modify it in order to set headers in the following way:

From Col1 to Col2 I want to use as headers the values in line 1.

From Col3 to last Col (in this case Col5), I want to use as headers the concatenation of line 1 to line 5.

The resulting table would look like this:

CustomerID	Name.1	Head2/Sub Head2/Sub Head2-1/Sub Head2-2/Phone	Head4/Sub Head4/Sub Head4-1/Sub Head4-2/State	Head5/Sub Head5/Sub Head5-1/Sub Head5-2/Country
48	Ana	2100-98	K	Y
9	George	93-993	T	P

How can this be reached?

Thanks for any help.

[ImkeF · Accepted solution]

Hi @cgkas ,

yes, this can be done.

1) Demote headers and select the first 5 rows of your table returns the material to work on

2) Transform this into a list of columns. This returns a nested list (of lists) whose elements you combine with "/".

3) Just replace the multiple occurrances of delimiters from the first columns "////" by nothing "" and the new column names are ready.

Paste the following code into the advanced editor to follow the steps along:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtIBoeDSJAWP1MQUIyS2CRLbVClWB0OtriGyahSeKZCHTQeK+Sg8UyAPoSMgIz8vFSRbklgCop3zS/NKiirBKkwsgAKOeYlA0sjQwEDXEsT3BuJIsLQlkOWeml+UDtJnaaxraWkMZISADFWKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CustomerID = _t, Name.1 = _t, Head2 = _t, Head4 = _t, Head5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CustomerID", Int64.Type}, {"Name.1", type text}, {"Head2", type text}, {"Head4", type text}, {"Head5", type text}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    #"Kept First Rows" = Table.FirstN(#"Changed Type1",5),
    NewColumnNames = List.Transform( Table.ToColumns( #"Kept First Rows" ), each Text.Replace(Text.Combine(_, "/"), "////", "") ),
    Custom2 = Table.RenameColumns ( #"Changed Type", List.Zip({Table.ColumnNames(Source), NewColumnNames})),
    #"Removed Top Rows" = Table.Skip(Custom2,4)
in
    #"Removed Top Rows"

[cgkas]

Hello @ImkeF,

Thanks so much for your help. It works excellent!

I was thinking how does this work. For example, why in step "Custom2" the new column names are:

"CustomerID" and not "CustomerID CustomerID"

or

"Head2/Sub Head2/Sub Head2-1/Sub Head2-2/Phone" and not "Head2 Head2/Sub Head2/Sub Head2-1/Sub Head2-2/Phone"

The output is correct, but why  if in List.Zip(..) for column3, the values are 

Head2

and

Head2/Sub Head2/Sub Head2-1/Sub Head2-2/Phone

The result of Table.RenameColumns (...) is only the second value and not the concatenation of 2 values? I hope make sense.

Thanks again

[ImkeF]

Hi @cgkas 

List.Zip create values pairs where the first value is the old column name and the second value the new column name.

You can easily follow this up if you manually rename some columns and then study the resulting code: A nested list with these value pairs (each list has 2 values).

[cgkas]

Excellent @ImkeF  Thanks for your help and share your knowlegde.