Forum Discussion
table transformation
Hi dufoq3 ,
I'm sorry, I'll write in detail what I have and what I expect.
I have this table that is compiled by hand on sharepoint by my management, unfortunately the format is decided by them for a question of visibility and their convenience.
| Mese | Jan-24 | |||
| WBS | Revenue | Cost | Delta EAC Revenue | Delta EAC Cost |
| P.00434.1.12 | 4000.00 | 2800.00 | 0.00 | 0.00 |
| P.00615.1.01 | 19000.00 | 15000.00 | 0.00 | 0.00 |
| P.00906.1.01 | 8700.00 | 8000.00 | 0.00 | 0.00 |
I would like to transform it into this format to have the possibility of better managing the data in power bi desktop and I would like to do this by transforming the data directly into power query
| Mese | WBS | Revenue | Cost | Delta EAC Revenue | Delta EAC Cost |
| 1/1/2024 | P.00434.1.12 | 4000.00 | 2800.00 | 0.00 | 0.00 |
| 1/1/2024 | P.00615.1.01 | 19000.00 | 15000.00 | 0.00 | 0.00 |
| 1/1/2024 | P.00906.1.01 | 8700.00 | 8000.00 | 0.00 | 0.00 |
This format is necessary for me to create graphs and formulas in dax later. This is what I think but there may also be a better solution, I'm open to any advice.
Thank you and sorry for the misunderstanding
I'm giving you last chance:
Could you answer my question no 2 and 3 please?
Hi, dufoq3
Sorry but I have already reported the data in the correct format.
2. what about [data] column in result? I don't see such column in Source: The date column is not there and it is called MESE as in the source table.
3. there are changes in Revenue and Cost but how? the data have not changed, they are the same numbers for each WBS and each MESE
I would like to bring the MESE row as a column before the WBS column, as described in the second tableWhat else should I write to you?
This is what I was speaking about... This just doesn't make sense
But if you only want to add Mese Date as column, you can achieve it like this (but it won't work with your real data probably because your samples are too poor)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k0tTlXSUfJKzNM1MgEyoChWJ1op3CkYyAxKLUvNKwWpcc4vLgFSLqk5JYkKro7OCggphBhYEUh3gJ6BgYmxiZ6hnqERUIWJgYEBUATIMrKAsZApmBYzQ1OgFgNDoLihJVyPoakBPk2WBmYwTRbmMIUW2LXEAgA=", 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]), #"Removed Top Rows" = Table.Skip(Source, each [Column1] <> "WBS"), #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]), Ad_Mese = Table.AddColumn(#"Promoted Headers", "Mese", each Date.From(Source{0}[Column2] & "-" & Text.From(Date.Year(DateTime.LocalNow()))), type date) in Ad_Mese