table transformation

Question

Hi everyone, I have a table like in the example belowI would like to transform it this waycan someone help me?

dufoq3 · Answer

This is what I was speaking about... This just doesn't make sense&nbsp;&nbsp;&nbsp;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)&nbsp;&nbsp;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] &lt;&gt; "WBS"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    Ad_Mese = Table.AddColumn(#"Promoted Headers", "Mese", each Date.From(Source{0}[Column2] &amp; "-" &amp; Text.From(Date.Year(DateTime.LocalNow()))), type date)
in
    Ad_Mese&nbsp;

dufoq3 · Answer

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 · Answer

Thank,there the data.MeseJan-24WBSRevenueCostDelta EAC RevenueDelta EACCostP.00434.1.124,000.00 €&nbsp;&nbsp;2,800.00 €&nbsp;&nbsp;-&nbsp;&nbsp; €&nbsp;&nbsp;-&nbsp;&nbsp; €&nbsp;P.00615.1.0119,000.00 €&nbsp;&nbsp;15,000.00 €&nbsp;&nbsp;-&nbsp;&nbsp; €&nbsp;-P.00906.1.018,700.00 €&nbsp;&nbsp;8,000.00 €&nbsp;&nbsp;-&nbsp;&nbsp; €&nbsp;&nbsp;-&nbsp;&nbsp; €&nbsp;&nbsp;and here's what I expectdatacommessa&nbsp;RevenueCostDelta EAC RevenueDelta EACCost1/1/2024P.00434.1.124.000,002.800,00001/1/2024P.00615.1.0150002000001/1/2024P.00906.1.018,700.00 €&nbsp;&nbsp;8,000.00 €&nbsp;00

dufoq3 · Answer

Ok, so you want transform from thisto thisbut you forgot DESCRIPTION!!!!I see that you renamed WBS to commensawhat about [data] column in result? I don't see such column in Sourcethere are changes in Revenue and Cost but how?

anonymous · Answer

Hi&nbsp;dufoq3&nbsp;,I'm sorry,&nbsp;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.MeseJan-24WBSRevenueCostDelta EAC RevenueDelta EACCostP.00434.1.124000.002800.000.000.00P.00615.1.0119000.0015000.000.000.00P.00906.1.018700.008000.000.000.00I 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 queryMeseWBSRevenueCostDelta EAC RevenueDelta EACCost1/1/2024P.00434.1.124000.002800.000.000.001/1/2024P.00615.1.0119000.0015000.000.000.001/1/2024P.00906.1.018700.008000.000.000.00&nbsp;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

Forum Discussion

table transformation

7 Replies

Related content

Table Transformation

Table Transformation

Table Transformation

Transform columns / type

Table Transformation

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 €	- €	- €

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