Month columns in excel to create common axis bar chart

[HP20]

I have data set in excel which has month as columns, and merged cells on top of months classify those into Target/ Actuals/ Estimate

                                   Target                        Actuals                    Estimates          

 Project                 Jan Feb Mar Apr        Jan Feb Mar Apr       May June July   

   A

   B

My question - I want to create a bar chart with months as common axis and with cumulative values and actual values as Y axis (attached screenshot), Somehow do I need to unpivote data, how can I ? I also need to calculate the cumulative vanues by month using DAX (I have formula but need to understand how can I use it as currently months are columns)

[vivran22 · Accepted solution]

Hello @HP20 ,

When importing data into Power Query, ensure that there is no merging of your headers

If it is a table, then it should look something like this:

Please apply the following steps in the Power Query:

• Transform > Transpose

• Home > Use the First Row as Headers

• Select the first column and Fill down:

• Select the three columns for project and unpivot

Output:

Hope this helps!

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[Ashish_Mathur]

Hi,

My Blog article here should help you - Rearrange a multi heading dataset into a single heading one which is Pivot ready.

[edhans · Accepted solution]

Hi @HP20 - You can try this. This will work even if your headers in Excel are merged. It will transform this:

into this:

The full M code is this:

let
    Source = Excel.Workbook(File.Contents("C:\Users\Ed Hansberry\OneDrive\Work Stuff\Power BI Forum Examples\Test Files\Pivot Data.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Transposed Table" = Table.Transpose(Sheet1_Sheet),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Merged Columns" = Table.CombineColumns(#"Filled Down",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {":Project"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute.1]), "Attribute.1", "Value"),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Date", each Date.FromText([Attribute.2] & "1, 2020"), type date
),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{":Project", "Project"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Target", Int64.Type}, {"Actuals", Int64.Type}, {"Estimates", Int64.Type}, {"Project", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Project", "Date", "Target", "Actuals", "Estimates"})
in
    #"Removed Other Columns"

If you copy and paste that into your Blank Query (directions below) and use this Excel file as your source you will be able to walk through the steps as to how I got there. You can then get a visual that looks like this:

My PBIX file is here. Once you have both the PBIX file and Excel file (linked above) you can change the source for the file to point to the Excel file on your hard drive, then Power Query will work.

If you need further help, please post some good sample data using the links below, not just text in the text box. You'll note I arbitrarly set the date to be the first of the month for 2020. If you have more data we may be able to create a better date that is more intelligent and will adapt to future years.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

One final note, you can see in my quick model I did not include a date table. you should have one really, and this link will help you create one in Power Query. Then you would use the date from the date table in your visual, and hide the date column in your FACT table. Creating a Dynamic Date Table in Power Query

[HP20]

Thank you I copied my data into your excel file and made few changes in code, it worked perfectly fine..thank you so much @edhans 

[edhans]

Glad I was able to assist @HP20 

[vivran22 · Accepted solution]

Hello @HP20 ,

When importing data into Power Query, ensure that there is no merging of your headers

If it is a table, then it should look something like this:

Please apply the following steps in the Power Query:

• Transform > Transpose

• Home > Use the First Row as Headers

• Select the first column and Fill down:

• Select the three columns for project and unpivot

Output:

Hope this helps!

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[HP20]

thanks @vivran22  this solution worked too..but as data had merged cells I used the other solution suggested, as I didnt want to add manual step..but this works perfectly fine thanks again!

[HP20]

@vivran22 thank you let me try this and get back 🙂

[vivran22]

@HP20 

Please confirm if this issue has been resolved. If yes, then mark the appropriate post as a solution as it will help others. 

If not, then please share more details along with sample dataset.

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[Greg_Deckler]

@HP20 - Short answer is yes, you almost certainly need to unpivot. @ImkeF @edhans might be able to better help with the Power Query to get you there.

[HP20]

@Greg_Deckler thanks ..looking forward to get help for unpivite process..