Create a Table from existing unrelated tables

[Arghya]

Dear All, 

I am completely new in Power BI and as a learner I am resorting to this community for getting help.

I have the following tables

Table_Jan

Net Bill Amnt            Billed Units

200                            100

300                            500

Table_Mar

Net Bill Amnt            Billed Units

230                            122

412                            398

Table_June

Net Bill Amnt            Billed Units

124                           115

78                             225

Essentially there are three tables as above and each represents monthly data like Jan, Mar and June

What I want is 

Table_New

Month         Avg. Net Bill Amnt                                                                 Avg. Billed Units

Jan              AVERAGE(Table_Jan[Net Bill])                                        AVERAGE(Table_Jan[Billed Units])

Mar            AVERAGE(Table_Mar[Net Bill])                                        AVERAGE(Table_Mar[Billed Units])

June           AVERAGE(Table_Jun[Net Bill])                                         AVERAGE(Table_Jun[Billed Units])

How can Accomplish this in Power BI??

Please help. 

[Ashish_Mathur]

Hi,

Using the Append feature of Query Editor, first append data from all three Tables and also have a column for Date (may be the dirst date of the month).  Create a Calendar table with an additional month column.  Create a relatioship from the Date column of the Data Table to the Date column of the Calendar Table.  Once that is done, you can simply drag the month column from the Calendar Table and then write the AVWRAGE() formulas.

[Greg_Deckler]

First, kudos for taking the time to properly ask your question! 

Second, see if this works:

Table = 
VAR tmpJan = ADDCOLUMNS(Table_Jan,"Month","Jan")
VAR tmpMar = ADDCOLUMNS(Table_Mar,"Month","Mar")
VAR tmpJune = ADDCOLUMNS(Table_June,"Month","June")
VAR tmpTable = UNION(tmpJan,UNION(tmpMar,tmpJune))
RETURN GROUPBY(tmpTable,[Month],
                "Avg. Net Bill Amnt",AVERAGEX(CURRENTGROUP(),[Net Bill Amnt]),
                "Avg. Billed Units",AVERAGEX(CURRENTGROUP(),[Billed Units])
        )

[Arghya]

Thanks!!

So I follow the steps as shown below

1. In modelling Tab, I click on New Table

2. And then in formula bar, I type what you have mentioned. 

I am getting error because I of the follwoing reasons

"UNION command can't be used with tables of different column numbers"

How to tackle this?

Regards,

[Greg_Deckler]

The error I see is about AVERAGEX and seems like you need to change your column type from Text to Numeric.

[Arghya]

Thanks!!

So I follow the steps as shown below

1. In modelling Tab, I click on New Table

2. And then in formula bar, I type what you have mentioned. 

I am getting error because I of the follwoing reasons

"UNION command can't be used with tables of different column numbers"

How to tackle this?

Regards,