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 ...
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

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

sing_ideas's avatar
sing_ideas
Regular Visitor
9 years ago

Hi v-yulgu-msft

 

Thanks for your clarification.

 

The same Unpivot Table option is available in Excel 2016, but it creates a new table and fills it with the values without any link to the source. Power BI is really great in making sure to have a Cross Table in Exce 2016 which is easier to do claculations and unpivot the same table in Power BI with link to the source table.

 

I am really glad to get a confirmation from an expert like you and thanks to the Power BI Product team.

 

regards,

Singaravelu S, CFP