Forum Discussion

sing_ideas's avatar
sing_ideas
Regular Visitor
9 years ago
Solved

Cross Table to Flat Table

Hi,

 

Is it a must to flatten the cross table to a flat table using the "Unpivot Table" option in Query Editor of Power BI Desktop?

 

If I do so will the table which I linked from the Excel File in One Drive (for Business) will get updated whenever the input data are changed in the Excel file which then changes the values of the cross table. This cross table is linked to Power BI Desktop and then flattened.

 

Note : what I do in Excel?

 

Table 1 has got the input values and Table 2 got the formulas where the calculated values generated. This Table 2 is a cross table, which I convert it to Flat Table.

 

regards

Singaravelu S, CFP

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi sing_ideas,

     

    Based on my test, the "Unpivot Table" option is the easiest and most appropriate solution to flatten a table. When you make any change to Excel File stored in One Drive, including changing data records (edit data values) or changing table structure (add or delete columns), the flat table will be updated automatically.

     

    Alternatively, if you can also achieve the flat table via DAX:

    Table =
UNION (
    SELECTCOLUMNS (
        'CrossTable',
        "Date", 'CrossTable'[Date],
        "Type", "Food",
        "Amount", 'CrossTable'[Food]
    ),
    SELECTCOLUMNS (
        'CrossTable',
        "Date", 'CrossTable'[Date],
        "Type", "Shelter",
        "Amount", 'CrossTable'[Shelter]
    ),
    SELECTCOLUMNS (
        'CrossTable',
        "Date", 'CrossTable'[Date],
        "Type", "Clothing",
        "Amount", 'CrossTable'[Clothing]
    )
)

    But this new calculated table cannot get the update if you add or delete columns in source file.

     

    Regards,
    Yuliana Gu

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    Very diffucult to tell from your descriptoin of the issue. Can you provide an example of your data and desired result?

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Microsoft Employee
        9 years ago

        Hi sing_ideas,

         

        Based on my test, the "Unpivot Table" option is the easiest and most appropriate solution to flatten a table. When you make any change to Excel File stored in One Drive, including changing data records (edit data values) or changing table structure (add or delete columns), the flat table will be updated automatically.

         

        Alternatively, if you can also achieve the flat table via DAX:

        Table =
UNION (
    SELECTCOLUMNS (
        'CrossTable',
        "Date", 'CrossTable'[Date],
        "Type", "Food",
        "Amount", 'CrossTable'[Food]
    ),
    SELECTCOLUMNS (
        'CrossTable',
        "Date", 'CrossTable'[Date],
        "Type", "Shelter",
        "Amount", 'CrossTable'[Shelter]
    ),
    SELECTCOLUMNS (
        'CrossTable',
        "Date", 'CrossTable'[Date],
        "Type", "Clothing",
        "Amount", 'CrossTable'[Clothing]
    )
)

        But this new calculated table cannot get the update if you add or delete columns in source file.

         

        Regards,
        Yuliana Gu