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 oldes...
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
This is my code, and data in excel https://docs.google.com/spreadsheets/d/1ugyIfdUkb22GXeFv2OKR_VeMM5X8pmhscjMruc8t8Lc/edit?usp=sharing
let
Source = Mail,
#"Filtered Rows" = Table.SelectRows(Source, each ([New_or_revisit] <> null) and ([case_type] = "CF")),
#"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows",{"Subject", "Folder Path", "Sender.Address", "Importance", "Index", "New_or_revisit", "case_type"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns2", "Categories", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Categories.1", "Categories.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Categories.1", type text}, {"Categories.2", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"sCRM_ID", "DateTimeReceived.1"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Value]), "Value", "DateTimeReceived.1"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Data completed"})
in
#"Removed Columns1"
hi, Anonymous
Add a group index column for
let
Source = Mail,
#"Filtered Rows" = Table.SelectRows(Source, each ([New_or_revisit] <> null) and ([case_type] = "CF")),
#"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows",{"Subject", "Folder Path", "Sender.Address", "Importance", "Index", "New_or_revisit", "case_type"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns2", "Categories", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Categories.1", "Categories.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Categories.1", type text}, {"Categories.2", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"sCRM_ID", "DateTimeReceived.1"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
Partition = Table.Group( #"Removed Columns" , {"sCRM_ID"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"DateTimeReceived.1", "Value", "Index"}, {"DateTimeReceived.1", "Value", "Index"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Value]), "Value", "DateTimeReceived.1"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Data completed"})
in
#"Removed Columns1"
Best Regards,
Lin
https://docs.google.com/spreadsheets/d/1k3zlkZ-IzZ2rhVrEY8Ui8FkKBfy6AcHbp3_SFnxMg98/edit?usp=sharing
I still don't have expected results with duplicates..
hi, Anonymous
I'm sorry for my carelessness, try this code:
let Source = Mail, #"Filtered Rows" = Table.SelectRows(Source, each ([New_or_revisit] <> null) and ([case_type] = "CF")), #"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows",{"Subject", "Folder Path", "Sender.Address", "Importance", "Index", "New_or_revisit", "case_type"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns2", "Categories", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Categories.1", "Categories.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Categories.1", type text}, {"Categories.2", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"sCRM_ID", "DateTimeReceived.1"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}), Partition = Table.Group( #"Removed Columns" , {"sCRM_ID"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"DateTimeReceived.1", "Value", "Index"}, {"DateTimeReceived.1", "Value", "Index"}), #"Pivoted Column" = Table.Pivot(#"Expanded Partition", List.Distinct(#"Expanded Partition"[Value]), "Value", "DateTimeReceived.1"), #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Data completed"}) in #"Removed Columns1"Best Regards,
Lin