Forum Discussion

Krcmajster's avatar
Krcmajster
Icon for Helper IV rankHelper IV
7 years ago
Solved

Creating other tables from one.

Hi all, 

 

I am new to power BI so apologise if this might be a stupid question 

 

I have big table, let's call it master table, and I want to create multiple other tables that are dynamically feeding from that master table. I don't want the the master to change. To simplify it, I want to do the same thing like you would do in excel when you put =CELLNAME to another tab. 

(Tab1 A1 is USA

Tab2 formula is ='tab1'A1)

 

Example:

 

Master Table 

 

ID    City       Sales    Nr. of Customers

1       City1    Sales1    Nr1

2       City2    Sales2    Nr2

3       City 3    Sales3    Nr3

4       City 4     Sales 4   NR4

 

 

new table

Nr. of Customers   Sales

nr1.                        sales1

nr.2                        sales2

Nr.3                        Sales3

Nr.4                        Sales4

 

 

I can create the new table manually but I can put calculated column that will take data from the master. There is no relation between them, it should be just "Copy Paste" column 

 

Thanks

Need Help

2 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi Krcmajster

     

    You can create Calculated Tables from the Modelling Tab>>>New Table

     

    Following functions can be useful for you. Check the documentation of these functions from links below and test them out for learning

     

    https://docs.microsoft.com/en-us/dax/data-analysis-expressions-dax-reference

     

    https://dax.guide/

    SELECTCOLUMNS
    SUMMARIZE
    SUMMARIZECOLUMNS
    ALL

    Examples

    New Table =
SELECTCOLUMNS (
    TableName,
    "Sales", [Sales],
    "Nr. of Customers", [Nr. of Customers]
)

     

    New Table =
ALL ( TableName[Sales], TableName[Nr. of Customers] )

     etc

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Krcmajster

    There are many ways in Power BI to create a new table from one table.

     

    One way is :

    In Queries Editor, create a new blank query, then paste the code like following one in the Advanced Editor,

    let
    Source =Sheet4,
    #"select Columns" = Table.SelectColumns(Source,{"Nr.of Customers", " Sales "})
in
    #"select Columns"

     

    This way, you need to know how to use Power Query or M language,

    Reference:

    Query Editor in Power Query

    M reference

     

    Another way is:

    Create a calculated table with DAX, 

    this way, you could follow Zubair_Muhammad's suggestion, it is generous of him to provide many functions to achieve a new table.

    Also, in this way, you could also get the calculated columns to the new table from master table.

     

    Best Regards

    Maggie