Forum Discussion
Very beginner level question: Graph budget when multiple years of budget is in a single row
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.
Community ChampionHey 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
If you are trying this on your sample data that you've shared it would work fine until the rows are being added and there is no change in your columns name.
The steps are not hardcoded but the column names do. If you change a name in your data source, this will stop working and then you've to amend those column names here as well.
