Hello @alexdi
now this requires some advanced coding. It includes to include the Group-id in your column names, foresee a renmaing process as well as deleting and adding process. I've tried this and found a solution. You have to change 2 queries. Here the code for it
//Groups
let
Source = Excel.CurrentWorkbook(){[Name="Groups"]}[Content],
AddKey = Table.AddColumn(Source, "Key", each Text.From([ID])&"-"&Text.From([Name]))
in
AddKey
//RecursiveMerge
let
Source = Excel.CurrentWorkbook
(
)
{[Name="RecursiveMerge"]}[Content
],
Source2 = Excel.CurrentWorkbook
(
)
{[Name="Users"]}[Content
],
#"Merged Queries" = Table.NestedJoin
(
Source2,
{"ID"},
Source,
{"ID"},
"Removed Columns",
JoinKind.LeftOuter
),
#"Expanded Removed Columns" = Table.ExpandTableColumn
(
#"Merged Queries",
"Removed Columns",
List.Skip
(
Table.ColumnNames
(
Source
),
2
)
),
SelectGroups = Table.FromRecords
(
Table.TransformRows
(
Table.AddColumn
(
Table.AddColumn
(
Table.ExpandTableColumn
(
Table.NestedJoin
(
Table.TransformColumnTypes
(
Table.SplitColumn
(
Table.FromList
(
List.Select
(
Table.ColumnNames
(
#"Expanded Removed Columns"
),
each Text.Contains
(
_,
"-"
)
),
Splitter.SplitByNothing
(
)
),
"Column1",
Splitter.SplitTextByDelimiter
(
"-"
)
),
{{"Column1.1", type number}}
),
"Column1.1",
Groups,
"ID",
"Groups"
),
"Groups",
{"Name"},
{"Name"}
),
"OldName",
each Text.From
(
[Column1.1]
)
&"-"&[Column1.2]
),
"NewName",
each Text.From
(
[Column1.1]
)
&"-"&[Name]
),
(
row
)
=> if row[NewName]=null then Record.TransformFields
(
row,
{"NewName", each row[OldName]&" / to be deleted"}
)
else row
)
),
RenameColumns = Table.RenameColumns
(
#"Expanded Removed Columns",
List.Zip
(
{SelectGroups[OldName],SelectGroups[NewName]}
)
),
ColumnsToAdd = List.Difference
(
Groups[Key
],
Table.ColumnNames
(
RenameColumns
)
),
AddColumns = Table.SelectColumns
(
RenameColumns,
Table.ColumnNames
(
RenameColumns
)
&ColumnsToAdd,
MissingField.UseNull
)
in
AddColumns
Try it out
I'm looking forward to your feedback
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
