Create table with data from different sheet

[SAllegri]

Hello,

I have several excel sheets, each one representing a different fiscal year and containing similar data: first column is company name, second is revenues, third is cost of goods sold.  

 A second sheet contains same data plus other.

I'd like to create tables in Power BI that contain one dataset (lets say the "Rev Growth") and the data is displayed with companies as columns and fiscal year as rows. Something as in the picture below

I hope I have been clear enough.

Thanks

[v-yanjiang-msft · Accepted solution]

Hi @SAllegri ,

According to your description, here's my solution.

1.Manually create a table contains all years in the column.

2.Add four custom columns.

Alpha = if [Year]=2019 then Table.SelectRows(FY2019,each [Company]="Alpha")[#"Rev Growth"]{0} else Table.SelectRows(FY2020,each [Company]="Alpha")[#"Rev Growth"]{0}

Beta = if [Year]=2019 then Table.SelectRows(FY2019,each [Company]="Beta")[#"Rev Growth"]{0} else Table.SelectRows(FY2020,each [Company]="Beta")[#"Rev Growth"]{0}

Charlie = if [Year]=2019 then Table.SelectRows(FY2019,each [Company]="Charlie")[#"Rev Growth"]{0} else Table.SelectRows(FY2020,each [Company]="Charlie")[#"Rev Growth"]{0}

Delta = if [Year]=2019 then Table.SelectRows(FY2019,each [Company]="Delta")[#"Rev Growth"]{0} else Table.SelectRows(FY2020,each [Company]="Delta")[#"Rev Growth"]{0}

Get the correct result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello @SAllegri,

You will have to do 3 things,

1. Merge tables, go on power querry > home > Merge queries. you select both sheets, select the columns that pairs and go with "Left Join".

2. Transform the colums in lines, so select all columns besides company and  "Rev Grow", go to Transform tab, and click on "transform columns in lines".

3. Click on the company colum, go to transform tab and click on "unpivot columns".

I think that this will help you, try out, I just done that last week. Hope that helps you

Bye and good luck!