Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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
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, this would work if i have duplicates with ID but with diffrent dates?
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
I have this error for some rows, can you help?
Expression.Error: There were too many elements in the enumeration to complete the operation.
Details:
List
hi, @Anonymous
Share some sample data with this error.
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
Hi,
this kind of work, but i have other row in the table i need to keep so it do not work in the way I want 😞 I will try to work more with this base and maybe I will come up with some solutions. Anyway your response was very helpful, thank you