Advice required on transforming from one format to another

[Rhothgar]

Afternoon,

I am struggling to graps how to transform a column into a row and add up values based on the value in another cell.

I've included coloured screenshots to aid understanding (more for me than anything).

So this is the input file.  I need to collate the information as per the output file below.

With this in mind, if Column D says "carge" and Column H says "GB" then the additive values need to tranpose into Cell E2 of the output file.

If Column D says "charge" and Column H is anything other than "GB" then the additive values need to transpose into Cell E3.

And so on and so forth for the other Column D values.




What ribbon tools should I be using to get this working please?  I am thinking I need some kind of index perhaps.

I've tried merging queries as new but both the fee column and gross column have to be populated with Column E of the output file so therefore the number of columns doesn't match!  I've stumbled at the first fence.

I did think about creating and additional column then perhaps merging it back into Column E if that makes sense.

Also the gross column in the input file DOES become the net column in the output file.  This is correct but may cause readers confusion.

And to boot I cannot exactly how to enter code (hopefully below is correct format)

INPUT FILE:

= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xVTbasMwDP0Vk6cNGldxru5bt+XyEDqWdQxWSskSZ21pLstl9PPnxA15XdPAwCBZIB0dHcubjUQJNqk0k1RMDG4sinWV22gfll+MO/wU+U7x0zxxPXKOX0n9Yp+PJ+2narNgDnROgBAEsADgoZUvbWcbSQYMhF/bUO8nrK0YhHFYors2SQYqg3W/QCKW5CVyyrCJ0ZqFaXU1dAcMWDcuuBdXwC6L4nSIwvqQZ4hHUMmSJos7zGi/U/3PVPUGFOU7fXfWLru6B/dB0DehJ995Aq1lbztvqyfkPAfo0VsGrj0WoSurYFPjxrCEeuNV69v+q2oEplVNw5R2iqkKNxo2rCno/POM9GlnRKkYEsEtH2pind7ER6xLvyvDosTHpqpTltW3Vxyevdg2GX2wMudDqVk86mfZ/gI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [gross = _t, fee = _t, net = _t, reporting_category = _t, description = _t, automatic_payout_id = _t, automatic_payout_effective_at_utc = _t, shipping_address_country = _t])

OUTPUT FILE:

= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY9BCoMwEEWvMmTVgsYQtDZLF/UA2lXFxdBEsGgiMUKP3xhLuwoEZjH/Td7vOsLKjImMM85JQo7X4qRWP3meU3ElfRJPQQq392KsAzg9lDXQoFPy7NeC01LE2Eq+ttXNSrv9HxaL1Sp4CJoXsUiDEi0MxkJtcZNwVziHm5RdoowaNi1399ah9rw8tP2qjLr8oW/h0Df99Y2C1bJM4xPdaDT4QnAcCoqFV+w/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, ref = _t, #"ex. Ref" = _t, Details = _t, Net = _t])

[lbendlin]

Something's missing in your input file. How do you map from Column E of the input to the column D of the output?  Is there another column that is cleaner?

[Rhothgar]

This is exactly what I am struggling to get my head around.  

I want to formulate column D from nothing effectively.  I suppose I could have a separate table where a blank in Column E of the input file directs what should appear in Column E of the output file?  I am afraid there isn't another cleaner column in the input file.

I've already excluded other columns in the example input file above because it was really messy with lots of alpha-numeric data that I didn't need in the output file.

I imagine it needs some kind of a loop function to scan each row and if the word "charge" is in Column D it then ascertains if Column H says GB or otherwise and then adds the figure from Column A to others which match and places them in cell E2 or E3 accordingly.

I'm guessing it would need some kind of M code?

[lbendlin]

Whatever works for your scenario.  As simple as you can make it.  Explicitly adding the value to your input table would be ideal.