Forum Discussion

romovaro's avatar
romovaro
Responsive Resident
8 months ago
Solved

Merge rows in one and transform field

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           
NameCIDCustomerCUIDCountry Long NameProject TypeProject ScopeContract Signed DateLOA Signature DateImplementation Window End DatePlanned # of EEsSegment
Amore SA - BR - 1001141BR011001141Amore SA1001141BR01BrazilCountry ProjectPayroll2022-03-31  50--
Amore SA - BR - 1001141BR011001141Amore SA1001141BR01-wdBrazilIntegration ProjectIntegration 12022-03-31  50--
Amore SA - BR - 1001141BR011001141Amore SA1001141BR01-sfBrazilIntegration ProjectIntegration 22025-05-30  50--
            
WHAT WE WANT           
Amore SA - BR - 1001141BR011001141Accor SA1001141BR01BrazilCountry ProjectPayroll2022-03-31  50--
Amore SA - BR - 1001141BR011001141Amore SA1001141BR01iBrazilIntegration ProjectADP Celergo Integration 12022-03-31  50--
Need Help
  • CPCARDOSO's avatar
    CPCARDOSO
    8 months ago

    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.

7 Replies

  • vivmueller's avatar
    vivmueller
    Kudo Kingpin
    8 months ago

    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.

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    8 months ago

    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's avatar
      romovaro
      Responsive Resident
      8 months ago

      HI Philip

       

      see below table sample:

       

      NameCIDCustomerCUIDCountry Long NameProject TypeProject ScopeContract Signed DateLOA Signature DateKick Off DateStatusActual/Schedule Go Live

      Renor SA - BR - 1001141BR01

      		1001141Renor SA1001141BR01BrazilCountry ProjectPayroll3/31/2025  On hold 

      Renor SA - BR - 1001141BR01i

      		1001141RenorSA1001141BR01-wdBrazilIntegration Project Integration WD3/31/2025  On hold12/31/2025
      Renor SA - BR - 1001141BR01i1001141Renor SA1001141BR01-sfBrazilIntegration Project Integration SF5/30/2026  On hold5/30/2026
      AMOR SA - BR - 1001141BR011001142Amor SA1001142BR01BrazilCountry ProjectPayroll5/31/2024  On hold 

      AMOR SA - BR - 1001141BR02

      		1001142Amor  SA1001142BR01-wdBrazilIntegration Project Integration WD3/31/2025  Live3/31/2025
      AMOR SA - BR - 1001141BR031001142Amor SA1001142BR01-orBrazilIntegration Project Integration OR5/30/2027  Under Implementation3/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.
       
      RenorSA1001141BR01-wd
      Renor SA1001141BR01-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:
       
       
      NameCIDCustomerCUIDCountry Long NameProject TypeProject ScopeContract Signed DateLOA Signature DateKick Off DateStatusActual/Schedule Go Live

      Renor SA - BR - 1001141BR01

      		1001141Renor SA1001141BR01BrazilCountry ProjectPayroll3/31/2025  On hold 

      Renor SA - BR - 1001141BR01i

      		1001141RenorSA1001141BR01-wdBrazilIntegration Project Integration WD3/31/2025  On hold12/31/2025

      vvv

  • CPCARDOSO's avatar
    CPCARDOSO
    Advocate III
    8 months ago

    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's avatar
      romovaro
      Responsive Resident
      8 months ago

      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

       

       

  • v-ssriganesh's avatar
    v-ssriganesh
    Community Support
    8 months ago

    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.