Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

table transformation

Hi everyone, I have a table like in the example below I would like to transform it this way can someone help me?
General Comment
Need Help
dufoq3's avatar
dufoq3
Community Champion
2 years ago

I'm giving you last chance:

Could you answer my question no 2 and 3 please?

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    dufoq3
    Community Champion
    2 years ago

    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