Power Query is not combining csv rows properly

[Herc]

I have about 40 csv files each with 2000 rows.

After using Power Query to combine the 40 files from folder, I get 332 errors.  When I investigate the errors I see the data has not imported properly for certain rows.  See pictures.

I the 2nd picture you can can see that instead of having 1 clean row with all my data, the data gets garbled up.  This only happens when I use PowerQuery to combine these files. Any ideas?

[Jayaselvan]

Hello @Herc,

The snaphots are really helpful. Could you please share the sample data, so it will be easy to find the solution?

Sharing the pbix file is super recommended. 

[Herc]

Here's the csv raw text data for Week 13.csv file

Issue Type,Issue key,Issue id,Project key,Project name,Project type,Project lead,Project description,Project url,Summary,Custom field (Epic Link),Due Date,Custom field (Dynamic Status Category),Created,Resolved,Status,Custom field (Need By),Custom field (Dependency Shared Service Group),Custom field (Target end),Custom field (Target start),Custom field (Commit Date),Custom field (Combined Sprints),Custom field (Work Type),Custom field (Story Points),Custom field (Team),Fix Version/s
Task,LFIS-713,559226,LFIS,Reg Reporting LFI Strategic,software,kmahant,,,"Data analysis, DQ, profiling and mapping for KDE ""Product Level 1 Description""",LFIS-1109,,Done,3/3/2022 7:36,3/7/2022 14:48,Closed,,,,,,,,2,4383,
Task,LFIS-712,559224,LFIS,Reg Reporting LFI Strategic,software,kmahant,,,"Data analysis, DQ, profiling and mapping for KDE ""Product Level 1 Node""",LFIS-1109,,Done,3/3/2022 7:34,3/7/2022 14:48,Closed,,,,,,,,2,4383,
Task,LFIS-710,557725,LFIS,Reg Reporting LFI Strategic,software,kmahant,,,This stroy is to tag FRDS Deposit transaction MV and hist table for NON PII attributes,LFIS-64,,Done,3/2/2022 8:36,3/9/2022 7:27,Closed,,,,,,,,1,4383,
Task,LFIS-709,557303,LFIS,Reg Reporting LFI Strategic,software,kmahant,,,Non Subledger View validation for Deposits KDE,LFIS-1,,Done,3/1/2022 22:38,3/15/2022 21:02,Closed,,,,,,,,2,4383,
Task,LFIS-708,557302,LFIS,Reg Reporting LFI Strategic,software,kmahant,,,Recon QA validation for Month to Date balance(MTD_AVG_BAL_AMT) amount LFI strategic,LFIS-1,,Done,3/1/2022 22:36,3/17/2022 8:48,Closed,,,,,,,,2,4383,

Here's the csv raw text data for Week 14.csv file

Issue Type,Issue key,Issue id,Project key,Project name,Project type,Project lead,Project description,Project url,Summary,Custom field (Epic Link),Due Date,Custom field (Dynamic Status Category),Created,Resolved,Status,Custom field (Need By),Custom field (Dependency Shared Service Group),Custom field (Target end),Custom field (Target start),Custom field (Commit Date),Custom field (Combined Sprints),Custom field (Work Type),Custom field (Story Points),Custom field (Team),Fix Version/s,Fix Version/s,Fix Version/s
Story,ITNSC-237,741616,ITNSC,Cloud - ITNSC Project,software,sbradbury,,,NET - Coordinate the Installation of SFP,FA-2059,,Done,7/29/2022 9:28,,Closed,,,,,,,,1,,,,
Story,ITNSC-146,720799,ITNSC,Cloud - ITNSC Project,software,sbradbury,,,NET - Bottomline DFW Test Connectivity,FA-2059,,Done,7/14/2022 9:55,,Closed,,,,,,,,1,,,,
Story,ITNSC-65,720797,ITNSC,Cloud - ITNSC Project,software,sbradbury,,,NET- Bottomline FCR Submittal,FA-2059,,Done,7/14/2022 9:54,,Closed,,,,,,,,1,,,,
Story,ITNS-3895,544092,ITNS,IT Network Services,software,sbradbury,"Network Services Project
- Team Telescrum (Voice Team)
- Team Packet Switchers (Network Team)",,NET- Bottomline DFW MPLS Circuit Delivery,FA-2059,,To Do,2/15/2022 10:46,,Backlog,,,,,,,,,1422,,,
Story,ITNS-3894,543314,ITNS,IT Network Services,software,sbradbury,"Network Services Project
- Team Telescrum (Voice Team)
- Team Packet Switchers (Network Team)",,NET- Bottomline CHD MPLS Circuit Delivery,FA-2059,,To Do,2/14/2022 21:25,,To Do,,,,,,,,,1422,,,
Story,ITFR-1475,1043849,ITFR,Finance Initiatives,software,pkotha,,,Year end maintenance for FPNA applications,FPAA-103,,To Do,11/14/2022 14:53,,To Do,,,,,,,,,,,,
Story,ITFR-1474,773295,ITFR,Finance Initiatives,software,pkotha,,,16092022 : Driver Regeneration of report,FPAA-124,,Done,8/16/2022 4:18,,Closed,,,,,,,,,,,,

I figure if you copy and paste this data and save as a csv you'll be able to recreate my sample files.

[Herc]

Hi Jayaselvan.  Thank you for your reply.

When combining them in Power Query this is what happens.

How do I include the sample files?  I don't have a pbix file just yet because I need to figure out how to combine this data first.