cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yakm Frequent Visitor
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: data transformation

hi, @yakm

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:

4.JPG

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
Community Support Team
Community Support Team

Re: data transformation

hi, @yakm

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:

4.JPG

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

yakm Frequent Visitor
Frequent Visitor

Re: data transformation

Hi, this would work if i have duplicates with ID but with diffrent dates?

Community Support Team
Community Support Team

Re: data transformation

hi, @yakm

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

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
yakm Frequent Visitor
Frequent Visitor

Re: data transformation

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

yakm Frequent Visitor
Frequent Visitor

Re: data transformation

Hi @v-lili6-msft

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

Community Support Team
Community Support Team

Re: data transformation

hi, @yakm

Share some sample data with this error.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
yakm Frequent Visitor
Frequent Visitor

Re: data transformation

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"

 

Community Support Team
Community Support Team

Re: data transformation

hi, @yakm

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
yakm Frequent Visitor
Frequent Visitor

Re: data transformation

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 389 members 3,552 guests
Please welcome our newest community members: