Table transformation, removing duplicates from KeyColum

Power Query
1

Hi,
hope you can help me on this.

How to transform the following table

id Test ID Type URL Speed
1 1 Upload URL1 Speed-1
2 1 Download URL2 Speed-2
3 2 Upload URL1 Speed-3
4 2 Download URL2 Speed-4
5 3 Upload URL1 Speed-5
6 3 Download URL2 Speed 6

In a table with a unique column TEST ID.

as the follow one (Could be a different one but similar).

Test ID xxx Xxx xxx Xxx xxx xxx
1 Upload URL1 Speed_1 Download URL2 Speed_2
2 Upload URL1 Speed_3 Download URL2 Speed_4
3 Upload URL1 Speed_5 Download URL2 Speed_6

Note: I can add 2 new tables to the Datamodel as below.
But I do not want to because I could have to add much more !

Test ID Type URL1 Speed
1 Upload URL1 Speed_1
2 Upload URL1 Speed_3
3 Upload URL1 Speed_5

Test ID Type URL1 Speed
1 Download URL2 Speed_2
2 Download URL2 Speed_4
3 Download URL2 Speed_6

Regards,
Fernando

3

Thanks for your patience on this one.

I’m struggle to get a good picture of a solution here. Would you be able to attach a simple example file to assist.

Thanks

4

Fernando,

Always try and provide a file even with 10 lines (fictitious if sensitive) and the exact data structure that you are working with, makes things so much quicker in terms of response time and often you get the solution sent back to you in the file.

Having 2 separate tables would seem to be the easiest solution. Pull both in as staging tables (load disabled) and merge queries either a left outer or right out will do it based on the dimension common to both tables. Then do a merge column (not merge query) to get it into the format above.

If that is a little confusing then consider sending a file and will send it back with the above included

5

See attach file.

Testes .xlsx (26.4 KB)

6

Please see the attached file.

Final%20Table
Final%20Table732×95 3.41 KB

You can click through the applied steps and see exactly what I did. But high-level:

  1. Added an index column from
  2. Added an integer-divided column by 2. I used two since the that is where the new record started using the index from #1
  3. Filter on Type = Upload
  4. Duplicate Query, and Filter on Type = Download. Renames as Download. Don’t
    need to load this to anything.
  5. Back in original query merge with the duplicated query in #4
  6. Expand the columns of what you want
  7. Remove any other columns not needed

Testes .xlsx (32.0 KB)

Hope that helps!

-Nick

Enterprise%20DNA%20Expert%20-%20Small