Combining two columns into one

Question

HI!&nbsp;I have a question (maybe a newby one)I have a table that has Drivers (1-9 code, which is defined in a separate table) and Variance which is Decimal value.These comes from 2024-2029 years. The problem is that they are in separate columns, and where Driver has a value, the Variance is empty.I want to see each "Variance" value next to each driver row, year by year.I hope it is clear.&nbsp;Thanks&nbsp;&nbsp;&nbsp;&nbsp;

ryan_mayu · Answer

could you pls paste the sample data (not the screenshot) and also provide the expected output in the table.

dinkadanka · Answer

The table itself looks like this:&nbsp;IDDriver*2024*FYDriver*2025*FYDriver*2026*FYDriver*2027*FYDriver*2028*FYDriver*2029*FYRev (@25BUD USD)*2024*TotalRev (@25BUD USD)*2025*TotalRev (@25BUD USD)*2026*TotalRev (@25BUD USD)*2027*TotalRev (@25BUD USD)*2028*TotalRev (@25BUD USD)*2029*TotalVariance*2025*VarianceVariance*2026*VarianceVariance*2027*VarianceVariance*2028*VarianceVariance*2029*Variance1000000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2 566 815 125&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2 566 815 125&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2 566 815 125&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2 566 815 125&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2 566 815 125&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2 566 815 125&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;2000000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4 876 948 139&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4 876 948 139&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4 876 948 139&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4 876 948 139&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4 876 948 139&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4 876 948 139&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;3090000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4 206 359 626&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 415 641 881&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 415 641 881&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 415 641 881&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 415 641 881&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 415 641 881-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4 994 081 567&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;And the expected outcome should look something like this:(Variance is basically yoy change, calculated in excel)I want to do some kind of waterfall in Powerbi which shows how revenue changes from year to year, driven by which driver.IDYearDriverRevVariance1202411001001202521202012026012001202701200120280120022024110010022025212020220260120022027012002202801200&nbsp;&nbsp;Thank you very muchD

anonymous · Answer

Hi All,Fisrtly&nbsp; ryan_mayu&nbsp;thank you for your solution!And&nbsp;DinkaDanka&nbsp;to my understanding, you need to transform the data to be arranged by rows in what was originally multi-column year data, and each of them has to be correctly related. Since pivoting the data will result in different Year columns, we need to aggregate the different columns to get the correct correlation, so we can organise your data into three tables and then aggregate them at the end.
After the Unpivot, then our ID column is used in the Year column for the merge, and the three tables are merged to get the correct relationship, I hope this helps!

&nbsp;
&nbsp;

If you have any other questions, check out the PBIX file I uploaded and I would be honoured if I could solve your problem!
Hope it helps!
Best regards,Community Support Team_ Tom Shen
If this post helps then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.
&nbsp;
&nbsp;

anonymous · Answer

Hi&nbsp;DinkaDanka&nbsp;,
Has your problem been solved after all this time, or has a new problem arisen, if there are any other questions on this issue, feel free to contact me and I'll get back to you as soon as I receive the message.
Hope it helps!
Best regards,Community Support Team_ Tom Shen
If this post helps then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

Forum Discussion

Combining two columns into one

4 Replies

Related content

Combine Column

Combining Column Data

Combine columns into 1

Tables combine

Combining Columns

ID	Driver2024FY	Driver2025FY	Driver2026FY	Driver2027FY	Driver2028FY	Driver2029FY	Rev (@25BUD USD)2024Total	Rev (@25BUD USD)2025Total	Rev (@25BUD USD)2026Total	Rev (@25BUD USD)2027Total	Rev (@25BUD USD)2028Total	Rev (@25BUD USD)2029Total	Variance2025Variance	Variance2026Variance	Variance2027Variance	Variance2028Variance	Variance2029Variance
1	0	0	0	0	0	0	2 566 815 125	2 566 815 125	2 566 815 125	2 566 815 125	2 566 815 125	2 566 815 125	-	-	-	-	-
2	0	0	0	0	0	0	4 876 948 139	4 876 948 139	4 876 948 139	4 876 948 139	4 876 948 139	4 876 948 139	-	-	-	-	-
3	0	9	0	0	0	0	4 206 359 626	415 641 881	415 641 881	415 641 881	415 641 881	415 641 881	- 4 994 081 567	-	-	-	-

ID	Year	Driver	Rev	Variance
1	2024	1	100	100
1	2025	2	120	20
1	2026	0	120	0
1	2027	0	120	0
1	2028	0	120	0
2	2024	1	100	100
2	2025	2	120	20
2	2026	0	120	0
2	2027	0	120	0
2	2028	0	120	0