table transformation

[Anonymous]

Hi everyone, I have a table like in the example below

I would like to transform it this way

can someone help me?

[dufoq3]

Hi, provide sample data as table so we can copy/paste. Don't forget to describe what exactly you want to do with the data. Don't forget to provide also expected result based on sample data.

[Anonymous]

Thank,

there the data.

Mese	Jan-24
WBS	Revenue	Cost	Delta EAC Revenue	Delta EAC Cost
P.00434.1.12	4,000.00 €	2,800.00 €	-   €	-   €
P.00615.1.01	19,000.00 €	15,000.00 €	-   €	-
P.00906.1.01	8,700.00 €	8,000.00 €	-   €	-   €

and here's what I expect

data	commessa	Revenue	Cost	Delta EAC Revenue	Delta EAC Cost
1/1/2024	P.00434.1.12	4.000,00	2.800,00	0	0
1/1/2024	P.00615.1.01	5000	2000	0	0
1/1/2024	P.00906.1.01	8,700.00 €	8,000.00 €	0	0

[dufoq3]

Ok, so you want transform from this

to this

but you forgot DESCRIPTION!!!!

1. I see that you renamed WBS to commensa
2. what about [data] column in result? I don't see such column in Source
3. there are changes in Revenue and Cost but how?

[Anonymous]

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

[dufoq3]

I'm giving you last chance:

Could you answer my question no 2 and 3 please?

[Anonymous]

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 table

What else should I write to you?

[dufoq3]

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