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
Icon for Microsoft Employee rankMicrosoft 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