Cross Table to Flat Table

[sing_ideas]

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

[Greg_Deckler]

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

[sing_ideas]

Hi,

Below are 3 images, CrossTable, Flat Table, Query Editor.

CrossTableFlatTableQueryEditorUnpivot

Hope, I am able to brief it clear with the screen shots.

External resources -

https://www.youtube.com/watch?v=pUXJLzqlEPk

http://nhsexcel.com/excel-pivot-table-crosstab-flat-list/

regards,

Singaravelu S

[v-yulgu-msft · Accepted solution]

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]

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