Merge rows in one and transform field

[romovaro]

HI all

Currently we have an excel file with customer details and a CUID with a unique number. CID is unique for the whole customer

What I need is from that file to create a table where we keep

Same row information for the Project Type = COuntry Project

And for the rest of rows (Integrations) we keep the first one with the closest date and CUID finish -i (instead of -wd or -sf)

any suggestions would be welcome

WHAT WE HAVE
Name	CID	Customer	CUID	Country Long Name	Project Type	Project Scope	Contract Signed Date	LOA Signature Date	Implementation Window End Date	Planned # of EEs	Segment
Amore SA - BR - 1001141BR01	1001141	Amore SA	1001141BR01	Brazil	Country Project	Payroll	2022-03-31			50	--
Amore SA - BR - 1001141BR01	1001141	Amore SA	1001141BR01-wd	Brazil	Integration Project	Integration 1	2022-03-31			50	--
Amore SA - BR - 1001141BR01	1001141	Amore SA	1001141BR01-sf	Brazil	Integration Project	Integration 2	2025-05-30			50	--
WHAT WE WANT
Amore SA - BR - 1001141BR01	1001141	Accor SA	1001141BR01	Brazil	Country Project	Payroll	2022-03-31			50	--
Amore SA - BR - 1001141BR01	1001141	Amore SA	1001141BR01i	Brazil	Integration Project	ADP Celergo Integration 1	2022-03-31			50	--

[v-ssriganesh]

Hi @romovaro,
Thank you for posting your query in the Microsoft Fabric Community Forum. Just replace your current Step 5 (the Grouped step) with these two lines:

IntegrationFuture = Table.SelectRows(IntegrationProjects, each [Actual/Schedule Go Live] <> null and [Actual/Schedule Go Live] > Date.From(DateTime.LocalNow())),

Grouped = Table.Group(IntegrationFuture, {"CID"}, { {"EarliestDate", each Table.FirstN(Table.Sort(_, {{"Actual/Schedule Go Live", Order.Ascending}}), 1), type table} })

That will fix the error and give you exactly the earliest future Go Live date per CID.

If you still get any error after this change, please share the exact error message or a screenshot.

Best regards,
Ganesh Singamshetty.

[CPCARDOSO · Accepted solution]

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:

1. Open Power Query in Excel or Power BI.
2. Paste this code into Advanced Editor.
3. Adjust:
   • The table name (Table1) to match your actual table.
   • Column names if they differ.
4. 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.

[romovaro]

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

[romovaro]

earliest date but in the future.

thanks

[PhilipTreacy]

@romovaro 

Please show an example of the result you want so we know exactly what data you want to keep and how you want it arranged.

Regards

Phil

[romovaro]

HI Philip

see below table sample:

Name	CID	Customer	CUID	Country Long Name	Project Type	Project Scope	Contract Signed Date	LOA Signature Date	Kick Off Date	Status	Actual/Schedule Go Live
Renor SA - BR - 1001141BR01	1001141	Renor SA	1001141BR01	Brazil	Country Project	Payroll	3/31/2025			On hold
Renor SA - BR - 1001141BR01i	1001141	RenorSA	1001141BR01-wd	Brazil	Integration Project	Integration WD	3/31/2025			On hold	12/31/2025
Renor SA - BR - 1001141BR01i	1001141	Renor SA	1001141BR01-sf	Brazil	Integration Project	Integration SF	5/30/2026			On hold	5/30/2026

AMOR SA - BR - 1001141BR01	1001142	Amor SA	1001142BR01	Brazil	Country Project	Payroll	5/31/2024			On hold
AMOR SA - BR - 1001141BR02	1001142	Amor  SA	1001142BR01-wd	Brazil	Integration Project	Integration WD	3/31/2025			Live	3/31/2025
AMOR SA - BR - 1001141BR03	1001142	Amor SA	1001142BR01-or	Brazil	Integration Project	Integration OR	5/30/2027			Under Implementation	3/31/2026

I started with a split: Integration and not integration

IsIntegration = IF (CONTAINSSTRING(OriginalPV[CUID],"-"),"Integration","Payroll")

And because in my new table i just want the CUID finalizing with "-i" I have this formula:

NormalizedCUID =

IF(OriginalPV[IsIntegration] = "Integration", LEFT(OriginalPV[CUID], FIND("-", OriginalPV[CUID],1)-1) & "-i", OriginalPV[CUID])

From the original file I can create a table showing only payroll:

OnlyPayroll = FILTER(OriginalPV, OriginalPV[IsIntegration] = "Payroll")

And the questions I have is for the Integration table.

For the first customer I have 2 CUIDs. I just need to show the one with the closest "Actual/Schedule Go Live" vs today. (but future date...past dates not needed) In that case the WD with GLD 12/31/2025.

RenorSA	1001141BR01-wd
Renor SA	1001141BR01-sf

I created a formula counting the days 

Datesfromtoday =

VAR Effective = OriginalPV[Actual/Schedule Go Live].[Date]

RETURN

IF

(OriginalPV[IsIntegration] = "Integration",

DATEDIFF(TODAY(), Effective, DAY),BLANK()

)

And created an IntegrationFuturetable:

IntegrationFuture =

FILTER(OriginalPV,

OriginalPV[IsIntegration] = "Integration" && OriginalPV[Actual/Schedule Go Live] > TODAY()

)

Now I am trying to filter so I only get one row:

MostRecentFuture =

VAR FutureRows =

FILTER(

    IntegrationFuture,

    IntegrationFuture[IsIntegration] = "Integration"

    && NOT ISBLANK(IntegrationFuture[AbsDayDiff])

)

RETURN

FILTER(

    ADDCOLUMNS(

        IntegrationFuture,

        "RankClosest",

        RANKX(

            FILTER(

                IntegrationFuture,

                [CID] = EARLIER([CID])

            ),

            IntegrationFuture[AbsDayDiff],

            ,

            ASC,Dense

        )

    ),

    [RankClosest] = 1

)

But I am getting: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

My last step would be merge both tables (Payroll only and Integration one)

FINAL TABLE = NATURALLEFTOUTERJOIN(PayrollOnly,IntegrationFUture)

Final table for the first customer:

Name	CID	Customer	CUID	Country Long Name	Project Type	Project Scope	Contract Signed Date	LOA Signature Date	Kick Off Date	Status	Actual/Schedule Go Live
Renor SA - BR - 1001141BR01	1001141	Renor SA	1001141BR01	Brazil	Country Project	Payroll	3/31/2025			On hold

Renor SA - BR - 1001141BR01i

1001141

RenorSA

1001141BR01-wd

Brazil

Integration Project

Integration WD

3/31/2025

On hold

12/31/2025

vvv

[vivmueller]

I would use Power Query to separate the whole table into different tables (queries), one for each customer. Then sort it descending by date, delete all rows keeping only the first one, and then replace the value of the one column. 
And after that you can append all the queries into a new one. Set all other queries to not load to the model.