Forum Discussion
Solved
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 ...
I have found solution in this good video.
https://youtu.be/UaPrpQOchFI?si=znjbz1oAcPg2uDS_
Thanks,Rizwan.
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.