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   ...
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.

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

     

     

    • romovaro's avatar
      romovaro
      Responsive Resident
      8 months ago

      earliest date but in the future.

      thanks