Forum Discussion
data transformation
hello,
I'm new to PowerBI and I have one issue with my report
The thing is I'm trying to "transpose" table like in the picture http://ifotos.pl/z/qwehnnw
* the red category always has oldest date
* in second table i can have id duplicates
hi, Anonymous
Based on my test, you could tr these step in Edit Queries:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUNzTUNzIwtASyi1JTlGJ14OJGMPGknNJUsIQRRMIYXQNU3ARZg056UWpqnlJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, category = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"category", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "category", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"category.1", "category.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"category.1", type text}, {"category.2", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ID", "Date"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Value]), "Value", "Date") in #"Pivoted Column"Result:
Best Regards,
Lin
10 Replies
hi, Anonymous
Based on my test, you could tr these step in Edit Queries:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUNzTUNzIwtASyi1JTlGJ14OJGMPGknNJUsIQRRMIYXQNU3ARZg056UWpqnlJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, category = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"category", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "category", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"category.1", "category.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"category.1", type text}, {"category.2", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ID", "Date"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Value]), "Value", "Date") in #"Pivoted Column"Result:
Best Regards,
Lin
hi, Anonymous
Yes, it will work well, you could try it by steps in the Edit Queries.
and here is my pbix file, please try it.
Best Regards,
Lin