Merging multiple CSVs and managing their dynamic Columns

[MRIZ]

I am new to Power Query. I am stuck in a situation . I will try to explain what i am trying and what i want to achieve. 
I have a sharepoint repo, where I may have multiple CSVs present in multiple folders. For example 
Parrent Folder / Child Folder A / EVT-ADT.csv
Parrent Folder / Child Folder B / EVT-ADT.csv
Attached csv file structure.  There are few things to note:
Both CSVs has same nature of data but for different projects. 
Both CSVs has these columns same Activity ID, Activity Name, Project ID, Project Name, Spreadsheet Field

Both CSVs might have same or different columns having DATES in it. 
Both CSVs has first ROW containg EVT-ADT.

csv 1:

csv 1

csv 2:

csv 2

Below are my requirements:
1- Loaded these files in Power BI (Done). 
2- Added a column which will have TABLE from BINARY (content) (Done)

= Table.AddColumn(#"Filtered Rows5", "Transform File (12)", each #"Transform File (12)"([Content]))
3- Expended the TABLEs column (Done). This step expended all tables and merged them. This output contains all the rows from both CSVs. 
= Table.ExpandTableColumn(#"Invoke Custom Function1", "Transform File (12)", Table.ColumnNames(#"Transform File (12)"(#"Sample File (12)")))
4- I want to remove the first ROW from each file (Done). 
5- Now tricky part starts. I want to make a generic columns for both the CSVs  so that both CSVs act as one file. below columns should be same but the columns having DATEs in it will be dynamic. The DATES column that are same in both CSVs will appear once but will contain cell values of there relevent files. The DATEs columns which are not same will becom new column (NEED HELP) Activity ID, Activity Name, Project ID, Project Name, Spreadsheet Field

I hope I am able to explain my issue. so plz  help me out. 

Tks. 

Rizwan. 

[MRIZ · Accepted solution]

I have found solution in this good video. 
https://youtu.be/UaPrpQOchFI?si=znjbz1oAcPg2uDS_ 

Thanks,

Rizwan.

[v-xinruzhu-msft]

Hi @MRIZ 

I'm glad you found a solution, and if the solution I provide can also help you, you can consider marking it as a solution so that it can be used as a reference for more people.

Best Regards!

Yolo Zhu

[MRIZ]

CSV 1:

EVT-ADT

Activity ID

Activity Name

Project ID

Project Name

Spreadsheet Field

4/14/2024

5/14/2024

6/14/2024

7/14/2024

8/14/2024

9/14/2024

10/14/2024

11/14/2024

12/14/2024

1/15/2024

2/15/2024

3/15/2024

4/15/2024

5/15/2024

6/15/2024

7/15/2024

8/15/2024

CPK-CS-D14-CE-P1-1

Escalator - Arcade 1

TKAR

2-ICCM-UP

Earned Value Cost

Earned Value Labor Units

Planned Value Cost

########

########

Planned Value Labor Units

469

1290

Estimate To Complete

########

Estimate To Complete Labor Units

1758

CSV 2: 

EVT-ADT

Activity ID

Activity Name

Project ID

Project Name

Spreadsheet Field

23-Dec

24-Jan

24-Feb

24-Mar

24-Apr

24-May

24-Jun

24-Jul

24-Aug

24-Sep

24-Oct

24-Nov

24-Dec

25-Jan

25-Feb

HP.CN.EXT.1010

Swimming Pool (Structure, Finishing & Equipment)

HPH W23 23-05

HPH Rev 02 BL1 Rev 03 W23 23-05

Earned Value Cost

Earned Value Labor Units

Planned Value Cost

57,383.21

########

########

8,197.60

Planned Value Labor Units

569

2031

2194

81

Estimate To Complete

57,383.21

########

########

8,197.60

Estimate To Complete Labor Units

569

2031

2194

81

[v-xinruzhu-msft]

Hi @MRIZ 

You can custom a function as the follwing.

(a as table)=>
    let 
    #"Promoted Headers" = Table.PromoteHeaders(a, [PromoteAllScalars=true]),
    #"Added Index" = Table.AddIndexColumn(#"Promoted Headers", "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Activity ID", "Activity Name", "Project ID", "Project Name", "Spreadsheet Field","Index"}, "Attribute", "Value"),
    Custom1 = Table.TransformColumns(#"Unpivoted Other Columns",{"Attribute", each if Text.Contains(_,"-") then _ else  Date.ToText(Date.FromText(_),[Format="yy-MMM"])}),
    #"Changed Type" = Table.TransformColumnTypes(Custom1,{{"Value", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Activity ID", "Activity Name", "Project ID", "Project Name", "Spreadsheet Field", "Index", "Attribute"}, {{"Sum", each List.Sum([Value]), type nullable number}}),
    #"Replaced Value" = Table.ReplaceValue(#"Grouped Rows",null,0,Replacer.ReplaceValue,{"Sum"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute]), "Attribute", "Sum"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

Then select the table in this function.

Then you can append the table after tranforming.

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.