Forum Discussion

Willhelm_GTBR's avatar
Willhelm_GTBR
New Member
2 years ago
Solved

How can I "flatten" a table for analytics

Greetings.

 

I am new to PowerBI, and in Data Analysis for that matter, and have in my hands an expenses table, with relevant data in both columns and rows, similar to this:

 

And I need, inside PowerBI, to flatten it so each entry has a supplier, CC, month and year, as such:

 

I need to reorder it so I can build a dash, showing how much was spent through the year and allowing filters by Supplier and/or CC. I thought of simply making new tables, for months, years, CC and Suppliers and sums from the source data then creating relations between them but am finding difficulties since I have zero training on the software.

 

Any help is much appreciated.

  • Hi, in Power Query pick Supplier and Cost Center columns go to Transform-Unpivot Dropdown (the arrow down) and choose Unpivot other columns

    Then you can Split by delimiter / the Attribute column and get your month and year columns

2 Replies

  • olgad's avatar
    olgad
    Icon for Resident Rockstar rankResident Rockstar

    Hi, in Power Query pick Supplier and Cost Center columns go to Transform-Unpivot Dropdown (the arrow down) and choose Unpivot other columns

    Then you can Split by delimiter / the Attribute column and get your month and year columns

    • Willhelm_GTBR's avatar
      Willhelm_GTBR
      New Member

      That's ridiculously easy and yet it elluded me. Worked like a charm.

       

      Now it is a matter of splitting months and year and relating everything, that should be no problem.

       

      Cheers, mate. Much obliged.