Forum Discussion
Merging multiple CSVs and managing their dynamic Columns
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.
I have found solution in this good video.
https://youtu.be/UaPrpQOchFI?si=znjbz1oAcPg2uDS_
Thanks,Rizwan.
4 Replies
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 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.
I have found solution in this good video.
https://youtu.be/UaPrpQOchFI?si=znjbz1oAcPg2uDS_
Thanks,Rizwan.
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
