Forum Discussion
Create Columns with given name if it doesn't exist.
Hi! I have some tables with different number of column with prefix "Level"
Example:
Table1 - Level1, Level2, Level3
Table2 - Level1, Level2, Level3, Level4
I want make sure all tables have Level1 ~ Level10.
How can I do that with powerquery? Thanks!
Hi Anonymous
What are the values in those newly created columns? Here is one sample to have the same value with the column name
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLlHSwUbFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [level1 = _t, level2 = _t, level3 = _t, level4 = _t]), columnList = {"level1","level2","level3","level4","level5","level6","level7","level8","level9","level10"}, fxAddColumn = (T as table, N as number) => [ newColumns=List.Difference(columnList, Table.ColumnNames(Source)), counter = List.Count(newColumns), columnName = newColumns{N}, tempTable = Table.AddColumn(T, columnName, each columnName), result = if N>= counter-1 then tempTable else @fxAddColumn(tempTable,N+1) ][result], Custom = fxAddColumn(Source,0) in Custom
4 Replies
Hi Anonymous
What are the values in those newly created columns? Here is one sample to have the same value with the column name
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLlHSwUbFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [level1 = _t, level2 = _t, level3 = _t, level4 = _t]), columnList = {"level1","level2","level3","level4","level5","level6","level7","level8","level9","level10"}, fxAddColumn = (T as table, N as number) => [ newColumns=List.Difference(columnList, Table.ColumnNames(Source)), counter = List.Count(newColumns), columnName = newColumns{N}, tempTable = Table.AddColumn(T, columnName, each columnName), result = if N>= counter-1 then tempTable else @fxAddColumn(tempTable,N+1) ][result], Custom = fxAddColumn(Source,0) in Customlet Source = #table(List.Transform({1..10}, each "Level" & Text.From(_)), {}), Other = #table({"Level1","Level3","Level5"},{{1,3,5}}), Appended = Source & Other in Appended
