Forum Discussion
Very beginner level question: Graph budget when multiple years of budget is in a single row
Hi,
Apologies for this very simple question, I just can't get my head around it.
I have data which in the most basic form is this:
ArticleName Budget in N-1 Budget in N Budget in N+1 Budget in N+2 (Where N = the current year)
Upgrading PC's 15 20 25 0
Buying mice 5 0 5 0
New laptops 30 40 50 60
I would like to see a graph with on the X-axis the year (either N-1,N, N+1,N+2 or even better 2021 , 2022, 2023 , 2024)
And on the Y axis the sum of the budget of the currently filtered rows. The filtering occurs by the user clicking on other visualisations.
Hey Jamie9979 ,
As per my interpretation of your requirement, you are looking for something like this:
If yes, this is how I achieved it:
1) In Power Query, I unpivoted the columns having budget
2) I split the column names to derive the year
3) Renamed the columns with meaningful names and removed unwanted column.
The M code looks like:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1IL0pMycxLVwhwjik1MDAyL1bSUTI0BRJGBiACxDJQitWJVnIqrQSpy81MTgWKQcRhNEjeL7VcISexoCS/AGSEMUjSBKwCRJgB1cQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Article = _t, #"Budget 2021" = _t, #"Budget 2022" = _t, #"Budget 2023" = _t, #"Budget 2024" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Article", type text}, {"Budget 2021", Int64.Type}, {"Budget 2022", Int64.Type}, {"Budget 2023", Int64.Type}, {"Budget 2024", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Article"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute.2", "Year"}, {"Value", "BudgetValue"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute.1"}) in #"Removed Columns"Also, attaching the pbix file here for your reference.
5 Replies
PC2790Community Champion
Hey Jamie9979 ,
As per my interpretation of your requirement, you are looking for something like this:
If yes, this is how I achieved it:
1) In Power Query, I unpivoted the columns having budget
2) I split the column names to derive the year
3) Renamed the columns with meaningful names and removed unwanted column.
The M code looks like:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1IL0pMycxLVwhwjik1MDAyL1bSUTI0BRJGBiACxDJQitWJVnIqrQSpy81MTgWKQcRhNEjeL7VcISexoCS/AGSEMUjSBKwCRJgB1cQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Article = _t, #"Budget 2021" = _t, #"Budget 2022" = _t, #"Budget 2023" = _t, #"Budget 2024" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Article", type text}, {"Budget 2021", Int64.Type}, {"Budget 2022", Int64.Type}, {"Budget 2023", Int64.Type}, {"Budget 2024", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Article"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute.2", "Year"}, {"Value", "BudgetValue"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute.1"}) in #"Removed Columns"Also, attaching the pbix file here for your reference.
That's an intresting solution, is it viable when dealing with thousands of articles though?
I've always thought that I should have more rows than columns in a dataset.
If more items were to get added I would have to keep extending the amount of columns which to my beginner brain seems like a weird thing to do, no?
- PC2790
There i sno hardcoding of columns here.
The code will pick up the latest data automatically with no manual intervention at all.
You can try once. 🙂
- AnkitKukreja
Super User
Hi! Jamie9979
Please see if this helps.
Then please unpivot your columns. You can refer the m code I've shared below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1IL0pMycxLVwhwjik1MDAyL1bSUTI0BRJGBiACxDJQitWJVnIqrQSpy81MTgWKQcRhNEjeL7VcISexoCS/AGSEMUjSBKwCRJgB1cQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"2021" = _t, #"2022" = _t, #"2023" = _t, #"2024" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"2021", Int64.Type}, {"2022", Int64.Type}, {"2023", Int64.Type}, {"2024", Int64.Type}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"2021", "2022", "2023", "2024"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Only Selected Columns",{{"Attribute", "Year"}})
in
#"Renamed Columns"If you are looking for something else, I would request you to share a sample pbix with your required solution.
Else, I would request
