Transforming Table

[Nks_Mr]

Hi everybody,

does anybody have a clue how to transform the following table from this 

to this: 

Thank you very much in advance!!!

BR

[Ritaf1983 · Accepted solution]

Hi @Nks_Mr 
M- code :
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQooys9KTS4BsgyNDAwMwLQpmAJiEyArNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, #"Budget FY 24" = _t, #"Budget FY 25" = _t, #"Sponsoring FY 24" = _t, #"Sponsoring FY 25" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Type", type text}, {"Budget FY 24", Int64.Type}, {"Budget FY 25", Int64.Type}, {"Sponsoring FY 24", Int64.Type}, {"Sponsoring FY 25", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "Type"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value", List.Sum),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute.2", "Year"}})
in
#"Renamed Columns"

You can also apply the steps with ux of Power query :

I attached pbix , so you can follow 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[Nks_Mr]

@Syk @Ritaf1983 

Hi both,

thank you very much! appreciate it!
I just have a question for a further extension for my model. So basically I used it to transform from this state:

into this:

However I have one question: As you can see, I have two zeros for ID 3 in for FY 24. Is it possible to delete the entries with only zeros (best before transforming - maybe to save loading time?) 

Sorry for asking, I'm really a rookie regarding PowerBI.

Thanks in advance!

[Ritaf1983]

Hi @Nks_Mr 
Yes, you can create a flag column that checks if the columns values are 0 , and than filter by this column...
But I suppose that the columns will be dynamic, so it is better to filter out zeros after transforming..because you will always have a budget and sponsoring column, no matter how many years of data you have.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[Syk · Accepted solution]

In Power Query, start with your data...

Select both budgeting columns and both sponsoring columns and in the Transform tab > unpivot columns.

Now under the Add Column tab (not transform) Select the "Attribute" column > Extract > Last Characters > Last 2 will give your your FY number, you can rename this column to FY.

Back in the transform tab... Select your "Attribute" column again > Extract > Text before delimiter > when the box pops up just put a space as your delimiter

Time to repivot. Select the "Attribute" column again and under Transform > Pivot Column > set the Values column to Value and hit OK 

[Ritaf1983 · Accepted solution]

Hi @Nks_Mr 
M- code :
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQooys9KTS4BsgyNDAwMwLQpmAJiEyArNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, #"Budget FY 24" = _t, #"Budget FY 25" = _t, #"Sponsoring FY 24" = _t, #"Sponsoring FY 25" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Type", type text}, {"Budget FY 24", Int64.Type}, {"Budget FY 25", Int64.Type}, {"Sponsoring FY 24", Int64.Type}, {"Sponsoring FY 25", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "Type"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value", List.Sum),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute.2", "Year"}})
in
#"Renamed Columns"

You can also apply the steps with ux of Power query :

I attached pbix , so you can follow 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.