Forum Discussion
Merge rows in one and transform field
Hello romovaro ...
It is possible to solve this using Power Query in Excel or Power BI (Microsoft Fabric), by creating a transformation logic to consolidate the rows according to your rule. I will explain how to do it. Try this:
--let
// 1. Source: adjust to your table name
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],// 2. Change column types (adjust as needed)
ChangedTypes = Table.TransformColumnTypes(Source,
{
{"Name", type text},
{"CID", type text},
{"Client", type text},
{"CUID", type text},
{"Country Long Name", type text},
{"Project Type", type text},
{"Project Scope", type text},
{"Contract Signing Date", type date},
{"LOA Signing Date", type date},
{"Implementation Window End Date", type date},
{"Planned # of EEs", Int64.Type},
{"Segment", type text}
}
),// 3. Filter Country Projects
CountryProjects = Table.SelectRows(ChangedTypes, each [Project Type] = "Country Project"),// 4. Filter Integration Projects
IntegrationProjects = Table.SelectRows(ChangedTypes, each [Project Type] = "Integration Project"),// 5. Group by CID and keep the row with the earliest Contract Signing Date
Grouped = Table.Group(IntegrationProjects, {"CID"},
{
{"EarliestDate", each Table.Sort(_, {{"Contract Signing Date", Order.Ascending}}){0}, type table}
}
),// 6. Expand the selected row
Expanded = Table.ExpandTableColumn(Grouped, "EarliestDate",
{"Name", "CID", "Client", "CUID", "Country Long Name", "Project Type", "Project Scope",
"Contract Signing Date", "LOA Signing Date", "Implementation Window End Date",
"Planned # of EEs", "Segment"}
),// 7. Adjust CUID to end with "i"
AdjustedCUID = Table.TransformColumns(Expanded,
{
{"CUID", each Text.BeforeDelimiter(_, "-") & "i", type text}
}
),// 8. Combine Country Projects + Adjusted Integration Projects
FinalResult = Table.Combine({CountryProjects, AdjustedCUID})
in
FinalResult--
Hoe to use:
- Open Power Query in Excel or Power BI.
- Paste this code into Advanced Editor.
- Adjust:
- The table name (Table1) to match your actual table.
- Column names if they differ.
- Click Done and load the data.
I think this code will help you.
Don’t forget the kudos, it costs you nothing and we help each other. Cheers.
Thanks CPCARDOSO
I tried. this is my code:
let
// 1. Source: adjust to your table name
Source = Excel.Workbook(File.Contents("C:\Users\rmontem\OneDrive - Automatic Data Processing Inc\Desktop\testdata.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
// 2. Change column types (adjust as needed)
ChangedTypes = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"CID", Any.Type}, {"Customer", type text}, {"CUID", type any}, {"Local Entity Name", type text}, {"Country Long Name", type text}, {"Work Type", type text}, {"Project Type", type text}, {"Project Scope", type text}, {"Contract Signed Date", type date}, {"LOA Signature Date", type any}, {"Implementation Window End Date", type any}, {"Planned # of EEs", Int64.Type}, {"Segment", type text}, {"Client Segment", type text}, {"Delivery Owner", type text}, {"Work Region", type text}, {"No. of Regular Cycles", type text}, {"Number of payruns per year", type any}, {"Roll Call Status", type text}, {"Roll Call Date", type date}, {"GV in client scope", type any}, {"Kick Off Date", type any}, {"Status", type text}, {"Actual/Schedule Go Live", type date}, {"Go live risk level", type text}, {"SR no. of GLD Change Request", type any}, {"Reason no SGLD/changed SGLD or project not green", type text}, {"Backlog assessment", type text}, {"Phase of change", type any}, {"GMV Portal Setup", type text}, {"Comment", type text}, {"Invoicing Requirements", type text}, {"Date T2S accepted", type any}, {"Sync/Desync with payroll project", type text}, {"Optional Integration Features", type any}, {"Sales Representative", type any}, {"Sales Office", type any}, {"Project Manager / Coordinator", type text}, {"Implementation Manager", type text}, {"Regional PM", type any}, {"Regional PM Manager", type any}, {"Implementation Consultant", type text}, {"Implementation Consultant Manager", type any}, {"Partner Name ", type text}, {"Global Solution Designer (GSD)", type any}, {"HQ Country", type text}, {"CAM Region", type text}, {"Client Distinction for NPS", type any}, {"T2S Type", type any}, {"CAM office", type text}, {"Client Account Manager (CAM)", type any}, {"Service Delivery Manager (SDM)", type any}, {"Currency", type text}, {"Start Value", type text}, {"Recurrent Starts Fee per Country (RSFC)", type text}, {"Recurrent Starts Fee per Entity (RSFE)", type text}, {"Implementation Fees", type text}, {"Sequence ID", Int64.Type}}
),
// 3. Filter Country Projects
CountryProjects = Table.SelectRows(ChangedTypes, each [Project Type] = "Country Project"),
// 4. Filter Integration Projects
IntegrationProjects = Table.SelectRows(ChangedTypes, each [Project Type] = "Integration Project"),
// 5. Group by CID and keep the row with the earliest Contract Signing Date / Actual/Schedule Go Live
Grouped = Table.Group(IntegrationProjects, {"CID"},
{
{"EarliestDate", each Table.Sort(_, {{"", Order.Ascending}}){0}, type table}
}
),
// 6. Expand the selected row
Expanded = Table.ExpandTableColumn(Grouped, "EarliestDate",
{"Name", "CID", "Client", "CUID", "Country Long Name", "Project Type", "Project Scope",
"Contract Signing Date", "LOA Signing Date", "Implementation Window End Date",
"Planned # of EEs", "Segment"}
),
// 7. Adjust CUID to end with "i"
AdjustedCUID = Table.TransformColumns(Expanded,
{
{"CUID", each Text.BeforeDelimiter(_, "-") & "i", type text}
}
),
// 8. Combine Country Projects + Adjusted Integration Projects
FinalResult = Table.Combine({CountryProjects, AdjustedCUID})
in
FinalResult
it seems getting error in Step5
BTW the date I need to show is the earliest Actual/Schedule Go Live", type date
earliest date but in the future.
thanks