Forum Discussion

Grant82's avatar
Grant82
Frequent Visitor
3 years ago

Merge Tables with Duplicate Column Names and Add Values

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    Grant82
    Frequent Visitor
    3 years ago

    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?

     

      • Grant82's avatar
        Grant82
        Frequent Visitor
        3 years ago

        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.