Merge Tables with Duplicate Column Names and Add Values

[Grant82]

I'm currently working on an email campaign where the platform drops tracking data each month in the form of CSV files. I've used Excel to look for the folder they get dumped in, grab all the CSV's in there, it then creates a table based on the column names of the first CSV and adds a row of monthly values under them from each new CSV it finds. I've just noticed however that the email platform creates a new but identical tracking variable each time you reuse a link in the email. So say you have a Technology link in the email at the top and bottom that go to the same URL - well the email platform will create a CSV that has two identical columns called Technology. My Excel currently only grabs one of the values so I'm not getting a sum, and I'm using Power BI to later render the data into visual stories. 

I have no power over the email platform so I believe I need to resolve this in Excel. Is there a way to change my approach above so that Excel sets up the table intially with the first CSV as it's doing, but then with the adding new CSV's, can it sum those columns with identical column names? Or, if there's another way to achieve what I want please do let me know.

My Power Query includes 7 steps-

Source

Filter Hidden Files1

Invoke Custom Function1

Renamed Columns1

Removed Other Columns1

Expanded Table Column1

Changed Type

Source

= Folder.Files("Z:\Grant\09 - Avature\New Build\Raw Data")

Filter Hidden Files1

= Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true)

Invoke Custom Function1

= Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content]))

Renamed Columns1

= Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"})

Removed Other Columns1

= Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"})

Expanded Table Column1

= Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))

Changed Type

= Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Email template", type text}, {"Total people", Int64.Type}, {"First email", type datetime}, {"Last email", type datetime}, {"No action", Int64.Type}, {"People who opened", Int64.Type}, {"Click-through", Int64.Type}, {"Invalid contact info", Int64.Type}, {"Bounced", Int64.Type}, {"Unsubscribed", Int64.Type}, {"People emailed", Int64.Type}, {"People reached", Int64.Type}, {"Marked as spam", Int64.Type}, {"Clicked - image:LinkedIn", Int64.Type}, {"Clicked - image:Facebook", Int64.Type}, {"Clicked - image:Instagram", Int64.Type}, {"Clicked - image:YouTube", Int64.Type}, {"Clicked - image:Twitter", Int64.Type}, {"Clicked - image:Diversity", Int64.Type}, {"Clicked - image:Equality", Int64.Type}, {"Clicked - image:Sustainability", Int64.Type}, {"Clicked - image:Partnerships", Int64.Type}, {"Clicked - image:Financial", Int64.Type}, {"Clicked - image:Legal", Int64.Type}, {"Clicked - image:Positivity", Int64.Type}, {"Clicked - image:Market", Int64.Type}, {"Clicked - image:Career Development", Int64.Type}, {"Clicked - image:Career Support", Int64.Type}, {"Clicked - image:Cul Research", Int64.Type}, {"Clicked - image:Country", Int64.Type}, {"Clicked - image:International", Int64.Type}, {"Clicked - image:Leadership", Int64.Type}, {"Clicked - image:Culture Tech", Int64.Type}, {"Clicked - image:Administrative", Int64.Type}, {"Clicked - image:Advisory", Int64.Type}, {"Clicked - image:Business Performance", Int64.Type}, {"Clicked - image:Business Processing/Operations", Int64.Type}, {"Clicked - image:Client Relationship Management", Int64.Type}, {"Clicked - image:Communications", Int64.Type}, {"Clicked - image:Corporate Services", Int64.Type}, {"Clicked - image:Corporate Sustainability", Int64.Type}, {"Clicked - image:Customer Services Management", Int64.Type}, {"Clicked - image:Data and Analytics", Int64.Type}, {"Clicked - image:Finance", Int64.Type}, {"Clicked - image:Human Resources", Int64.Type}, {"Clicked - image:Secretariat", Int64.Type}, {"Clicked - image:Marketing", Int64.Type}, {"Clicked - image:Branch/Contact Centre Management", Int64.Type}, {"Clicked - image:Procurement", Int64.Type}, {"Clicked - image:Product Management/Development", Int64.Type}, {"Clicked - image:Project Services", Int64.Type}, {"Clicked - image:Job Research", Int64.Type}, {"Clicked - image:Risk Compliance", Int64.Type}, {"Clicked - image:Sales", Int64.Type}, {"Clicked - image:Strategy Management", Int64.Type}, {"Clicked - image:Job Tech", Int64.Type}, {"Clicked - image:Trading Services", Int64.Type}, {"Clicked - Here", Int64.Type}, {"Clicked - visit our career portal", Int64.Type}})

And an example of the data

Column Name--------Value
Email template--------Global - EN - Blank Email Template - Theme 2
Total people--------2
First email--------2023-04-29 08:38:21
Last email--------2023-04-29 08:38:21
No action--------1
People who opened--------1
Click-through--------1
Invalid contact info--------0
Bounced--------0
Unsubscribed--------0
People emailed--------2
People reached--------2
Marked as spam--------0
Clicked - image:LinkedIn--------1
Clicked - image:Facebook--------1
Clicked - image:Instagram--------1
Clicked - image:YouTube--------1
Clicked - image:Twitter--------1
Clicked - image:Diversity--------1
Clicked - image:Equality--------1
Clicked - image:Sustainability--------1
Clicked - image:Partnerships--------1
Clicked - image:Financial--------1
Clicked - image:Legal--------1
Clicked - image:Positivity--------1
Clicked - image:Market--------1
Clicked - image:Career Development--------1
Clicked - image:Career Support--------1
Clicked - image:Cul Research--------1
Clicked - image:Country--------1
Clicked - image:International--------1
Clicked - image:Leadership--------1
Clicked - image:Culture Tech--------1
Clicked - image:Administrative--------1
Clicked - image:Advisory--------1
Clicked - image:Business Performance--------1
Clicked - image:Business Processing/Operations--------1
Clicked - image:Client Relationship Management--------1
Clicked - image:Communications--------1
Clicked - image:Corporate Services--------1
Clicked - image:Corporate Sustainability--------1
Clicked - image:Customer Services Management--------1
Clicked - image:Data and Analytics--------1
Clicked - image:Finance--------1
Clicked - image:Human Resources--------1
Clicked - image:Secretariat--------1
Clicked - image:Marketing--------1
Clicked - image:Branch/Contact Centre Management--------1
Clicked - image:Procurement--------1
Clicked - image:Product Management/Development--------1
Clicked - image:Project Services--------1
Clicked - image:Job Research--------1
Clicked - image:Risk Compliance--------1
Clicked - image:Sales--------1
Clicked - image:Strategy Management--------1
Clicked - image:Job Tech--------1
Clicked - image:Trading Services--------1
Clicked - Here--------1
Clicked - visit our career portal--------1

Screenshot of the CSV and an example of the duplicate tracking names-

Thanks guys!! 

[Grant82]

Without further help to implement the above is unfortunately no use to me - please do not think I am giving negative feedback towards the solution - I just do not understand it and would need further help here. Moving forward then I would love another solution or suggestion on how to tackle my problem. The method I have been considering is to setup the intitial CSV with 4 or 5 containers for every single variable. Then my power query instead of just merging tables would look for those 4 or 5 containers, sum the values, and create a new container called (giving an example of one variable here) "Job Tech Sum". Then it produces the table based on those Sum columns instead of any of the base one out putted by the campaign system. Let me give an example of what I mean here.

CSVs would look something like this

	A	B	C	D	E	F	G	H	I	J
1	Source.name	job tech	job tech_1	job tech_2	job tech_3	job tech_4	job tech_5	cul tech	cul tech_1	cul tech_2
2	Dummy1.csv	1						1
3	Dummy2.csv	1		1		1		1	1	1

So when I merged previously, only the first column name would create the table because there are no values in the other columns. By doing the above, I will need to limit the number of times a variable can appear in the email to a max of 5, and instead of just merging the tables, I now want to setup the powery to first look for columns B to G, sum the values, and create a new column called "Job Tech Sum" which has the total value for that month (or row).

This level of solution is something I'm able to understand but I am not sure how to do the checking, summing, or creation of a new column in power query.

For some insight, I am a HR guy and this is a project aimed at figuring out what type of informaiton will help people the most. I am not getting paid for this - it's purely to try to help people as a side project for the company I work for. I've learned everything I know on this in the last month, and I'm trying my best. If there is somebody out there with a little patience to help an averagely intelligent HR guy finish a tech project - well there may be a place in heaven for you.

[v-cgao-msft]

Hi @Grant82 ,

Try transpose table first.

then group.

All codes in advance editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1VVNUxsxDP0rmpybIQ09UG4QoKQDNEPSznQYDopXZNV47a1sL8O/r3YThrTjXc49OFlJz/p4ku2Hh9HM21Q5uMOKRh9GlxWyhUhVbTG2ipWPaKEmX9tWvGIJEahFqXSDB8KdBzSRvdPvRYeH59KDr8lRobqZZbMdx1J82pQqz12Dlgsw3kXdCOyevKrPfXKm2/DdhbQORnjdiXufXbhDhRCaslPcomypAAwQaqxeQ6pmDFzhhk5v2Kk4dxnTFRpae7/NmOYuRNxI1uNPn1ZpTRnL6pljJMlYLrghCRxfMrbL30lJyZqWSdNgh2vuASxQolPPJdchV6HudYbR5oihTVa/8JonN/l4Hd0xY5ihEAlcUEPW1xW5AdAy1bWXLCBZuKdAKKbMmXVOouTymjvl3WE7ij3FYrGjKR81JiFYUTbqWVGx4xBF3Te5tp8VDQefzes8BXYUAixInrxU2o2chzeYeKP/7DZH32qSrqBcY1VWipUru4NoYXCLTk191PuqSo5Nv0cv2hS9AGBJ0rBmMQx6bzJnCvBV2+29u+H8LjAioCvgTBv4Etn0j3OOwOuk1Laj45PkU1+SEYoojLnwu7lW2nPNEQ1aHs32d9ZMC9BhGaymbaNOVL+1SOrpzcXR8LnRDb9INwx05qtfD52cew5b0BmoLfcwuESb562de9q8DBeM+L7ymuTvwHpqOIJ2DMzuYmhvBT2+jx8eRj/Qphb9xfq1PkZjuLzTn3OLbgu7B2u1f7BUvSo1L5gqvFuT6fF48mk8/QyTk9Pjk9Ppx17t4ZocrFdfk38w/896fPwD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, Column14 = _t, Column15 = _t, Column16 = _t, Column17 = _t, Column18 = _t, Column19 = _t, Column20 = _t, Column21 = _t, Column22 = _t, Column23 = _t, Column24 = _t, Column25 = _t, Column26 = _t, Column27 = _t, Column28 = _t, Column29 = _t, Column30 = _t, Column31 = _t, Column32 = _t, Column33 = _t, Column34 = _t, Column35 = _t, Column36 = _t, Column37 = _t, Column38 = _t, Column39 = _t, Column40 = _t, Column41 = _t, Column42 = _t, Column43 = _t, Column44 = _t, Column45 = _t, Column46 = _t, Column47 = _t, Column48 = _t, Column49 = _t, Column50 = _t, Column51 = _t, Column52 = _t, Column53 = _t, Column54 = _t, Column55 = _t, Column56 = _t, Column57 = _t, Column58 = _t, Column59 = _t, Column60 = _t]),
    #"Transposed Table" = Table.Transpose(Source),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Grouped Rows" = Table.Group(#"Promoted Headers", {"Column Name"}, {{"Value", each try List.Sum(List.Transform([Value],each Number.From(_))) otherwise [Value]{0}}})
in
    #"Grouped Rows"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Grant82]

That looks like exactly what I want to do - thank you! I'm sorry to say I'm struggling to work out how/where to implement this though - I'm pretty new to power query and dex. 

So in power query editor (is that advanced editor as you referred to?) I have done this-

I then added a transpose in here-

I then added a step to group and pasted in your code (I had to remove the "Let  Source" so it would run)-

But I'm obviously not getting it right. 🙂

Could you walk me through it please?

[v-cgao-msft]

Hi @Grant82 ,

You can create a new blank query and paste the above code into the advanced editor to reference the steps.

You can also watch ImkeF's video to learn how to integrate M-code into your existing solution.

Power BI Forum Help: How to integrate M-code into ... - Microsoft Fabric Community

Hope these help.

Best Regards,
Gao
Community Support Team

[Grant82]

Thank you I've watched the video and tried my best, but still need some help I'm sorry to say. I have transposed and then used the advanced editor to add the grouping step with the code you've supplied. It has not produced the desired effect unfortunately. The final grouping table seems to show just one month's data now, but it has added a row at the bottom called AA which has 2 in it  - I'm not too sure what that number is either. 

With these screenshots:

1. both dates are in the same row of data

2. those values look like the values from the first month only

3. aa row 

But I'm using this data to track these values over time so I need a row for each month. Also, with this data I had the first CSV having only one of each variable name. Then in the second month it created 3 idential "Job Tech" variables with values in each - I thought this may sum the values of them for that month (I'm just confirming we're on the same page). 

Have I incorrectly implemented your solution or was it not designed to work how I expected it? Sorry if I explained poorly what I'm looking for!

I do very much appreciate your help.