Forum Discussion
Month in Column. Need it in rows for adding slicer. When tried unpivot, other values get repeated.
Hi all,
I am new and learning to use Power BI.
In my work,
1. I came across a situation where monthly spend / plan for remaining of the year is provided. Means, the value till last month will be actual spend and value from current to future month will be Budget Plan.
2. The months are shown in Columns. So, If I want to add slicer for month, I needed it in rows. So, I tried to have a Unpivot. But, the other values get repeated. Means, the total burget, Future budget and Actual Budget spend repeats more than once based upon the month repeats. Thus, When I create a sum of Budget, it shows inflated numbers. I am struggling to correct this.
3. The input has 2 sheets for 2 departments. I am in need to show the trend for both individually (with trend of actual spend and also what is potential spend for rest of the year) and a dashboard for both together.
Can someone guide on how to proceed with this scenario please?
Below is a example of the data
Regards,
Avinaash
Hello avinaashmech1 -
Limiting the columns to only those necessary prior to the unpivot will prevent the duplicated rows. Please see the example below. Note, this produces a table for the actuals. I recommend you use a separate table for the total budget and then create measures to calculate the potential spend for the rest of the year.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVS5bgIxEP2ViJrCM57D7qO0KZIOUaElQeISyRb8fWzjZa8BKdIg4xnve3OvVovX9nC4voAslou39nLc/baXJv03xbNz6YiEbqjOFwyAMYNIDMyQNQ5psV7eGTTpPpvN9/G0P31d00UpBpNmit+/RVVih9kVQvUoBk94EslDyt4GLggXLlBfnJ4SxGkg+SvkqctJWJybqvuXEVQN/BL7PAAcQtl0eiuPvxvRc1A0KHJ93rfb3aZ5+WjP5/2u+RkCjbhGl0oxsiCx5kJQBLbI0MjXc6nA+acASqUnwq0DJuD+3+AVGJzEkmogTHUyoOlJI92ljkQtSE1PqErXxyGOyRc2QiCDjR93VVdXi2o2kh1pcSOWuOCm8USU+01RwEql2KnsAOcUozkd90w1ITooZxQxZhVnO2HkuHEZCiiEAi5pZtPJrGTN08OFMB5Pa1hrdjuTpxByN4sXYYNpthkCzyDnUnNV34YYMwUIWVvUT5eDaAg5QBoy0YR23Cu9VeX2MWh0VOqTdgf33bn+Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Initiative = _t, #" Category" = _t, January = _t, February = _t, March = _t, April = _t, May = _t, June = _t, July = _t, August = _t, September = _t, October = _t, November = _t, December = _t, #"Budget Spent till Last month(2024)" = _t, #"Required budget for rest of the year (2024)" = _t, #"Total Budget (2024)" = _t, Year = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Initiative", type text}, {" Category", type text}, {"January", Int64.Type}, {"February", Int64.Type}, {"March", Int64.Type}, {"April", Int64.Type}, {"May", Int64.Type}, {"June", Int64.Type}, {"July", Int64.Type}, {"August", Int64.Type}, {"September", Int64.Type}, {"October", Int64.Type}, {"November", Int64.Type}, {"December", Int64.Type}, {"Budget Spent till Last month(2024)", Int64.Type}, {"Required budget for rest of the year (2024)", Int64.Type}, {"Total Budget (2024)", Int64.Type}, {"Year", Int64.Type}}), #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Initiative", " Category", "Year", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Initiative", " Category", "Year"}, "Attribute", "Value"), #"Inserted Merged Column" = Table.AddColumn(#"Unpivoted Other Columns", "Date", each Date.From ( Text.Combine({[Attribute], " 1, ", Text.From([Year], "en-US")}) ), type date) in #"Inserted Merged Column"
3 Replies
- PwerQueryKees
Super User
Ok.
- First, make sure your sheets use excel tables instead of ranges. Not sure what happened, but PowerQuery gave errors when calling the CurrentWorkbook() function.
- You can't have it both ways... Seperate rows is separate rows. So the rest of the columns have to be replicated.
- You probably have to split your table in two separate tables. One for the key and summary values and one for the monthly details.
The remainder of you question is not actually PowerQuery related if I understand you correctly. Can't help you there I am afraid...
- jennratten
Hello avinaashmech1 -
Limiting the columns to only those necessary prior to the unpivot will prevent the duplicated rows. Please see the example below. Note, this produces a table for the actuals. I recommend you use a separate table for the total budget and then create measures to calculate the potential spend for the rest of the year.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVS5bgIxEP2ViJrCM57D7qO0KZIOUaElQeISyRb8fWzjZa8BKdIg4xnve3OvVovX9nC4voAslou39nLc/baXJv03xbNz6YiEbqjOFwyAMYNIDMyQNQ5psV7eGTTpPpvN9/G0P31d00UpBpNmit+/RVVih9kVQvUoBk94EslDyt4GLggXLlBfnJ4SxGkg+SvkqctJWJybqvuXEVQN/BL7PAAcQtl0eiuPvxvRc1A0KHJ93rfb3aZ5+WjP5/2u+RkCjbhGl0oxsiCx5kJQBLbI0MjXc6nA+acASqUnwq0DJuD+3+AVGJzEkmogTHUyoOlJI92ljkQtSE1PqErXxyGOyRc2QiCDjR93VVdXi2o2kh1pcSOWuOCm8USU+01RwEql2KnsAOcUozkd90w1ITooZxQxZhVnO2HkuHEZCiiEAi5pZtPJrGTN08OFMB5Pa1hrdjuTpxByN4sXYYNpthkCzyDnUnNV34YYMwUIWVvUT5eDaAg5QBoy0YR23Cu9VeX2MWh0VOqTdgf33bn+Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Initiative = _t, #" Category" = _t, January = _t, February = _t, March = _t, April = _t, May = _t, June = _t, July = _t, August = _t, September = _t, October = _t, November = _t, December = _t, #"Budget Spent till Last month(2024)" = _t, #"Required budget for rest of the year (2024)" = _t, #"Total Budget (2024)" = _t, Year = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Initiative", type text}, {" Category", type text}, {"January", Int64.Type}, {"February", Int64.Type}, {"March", Int64.Type}, {"April", Int64.Type}, {"May", Int64.Type}, {"June", Int64.Type}, {"July", Int64.Type}, {"August", Int64.Type}, {"September", Int64.Type}, {"October", Int64.Type}, {"November", Int64.Type}, {"December", Int64.Type}, {"Budget Spent till Last month(2024)", Int64.Type}, {"Required budget for rest of the year (2024)", Int64.Type}, {"Total Budget (2024)", Int64.Type}, {"Year", Int64.Type}}), #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Initiative", " Category", "Year", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Initiative", " Category", "Year"}, "Attribute", "Value"), #"Inserted Merged Column" = Table.AddColumn(#"Unpivoted Other Columns", "Date", each Date.From ( Text.Combine({[Attribute], " 1, ", Text.From([Year], "en-US")}) ), type date) in #"Inserted Merged Column"Thanks much! Really helpful
