Forum Discussion
Errors when plotting week numbers
Greetings,
When I plot week numbers by transposing the week columns, power BI complains I have double values in the untransposed columns... What to do?
Hi Anonymous ,
You can add an index column before un-pivoting your data.
This will prevent your plotting of weeks from getting duplicated.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqlHQhQAFGAvGwKSVYnVAGhwLCnJSFRSA4q6JxSVghqGBAoQ2gtKmYJpcDU6JeUAIEg9PhWoAywNpMyhtoUCuhlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"| Product | Region | Week1 | Week2 | Week3 |" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"| Product | Region | Week1 | Week2 | Week3 |", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "| Product | Region | Week1 | Week2 | Week3 |", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"| Product | Region | Week1 | Week2 | Week3 |.1", "| Product | Region | Week1 | Week2 | Week3 |.2", "| Product | Region | Week1 | Week2 | Week3 |.3", "| Product | Region | Week1 | Week2 | Week3 |.4", "| Product | Region | Week1 | Week2 | Week3 |.5", "| Product | Region | Week1 | Week2 | Week3 |.6", "| Product | Region | Week1 | Week2 | Week3 |.7"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"| Product | Region | Week1 | Week2 | Week3 |.1", type text}, {"| Product | Region | Week1 | Week2 | Week3 |.2", type text}, {"| Product | Region | Week1 | Week2 | Week3 |.3", type text}, {"| Product | Region | Week1 | Week2 | Week3 |.4", type text}, {"| Product | Region | Week1 | Week2 | Week3 |.5", type text}, {"| Product | Region | Week1 | Week2 | Week3 |.6", type text}, {"| Product | Region | Week1 | Week2 | Week3 |.7", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"| Product | Region | Week1 | Week2 | Week3 |.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"| Product | Region | Week1 | Week2 | Week3 |.2", "Product"}, {"| Product | Region | Week1 | Week2 | Week3 |.3", "Region"}, {"| Product | Region | Week1 | Week2 | Week3 |.4", "Week1"}, {"| Product | Region | Week1 | Week2 | Week3 |.5", "Week2"}, {"| Product | Region | Week1 | Week2 | Week3 |.6", "Week3"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"| Product | Region | Week1 | Week2 | Week3 |.7"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns1",1),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Top Rows",{{"Week1", Int64.Type}, {"Week2", Int64.Type}, {"Week3", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type2", "Index", 0, 1, Int64.Type),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Product", "Region", "Index"}, "Attribute", "Value")
in
#"Unpivoted Columns"
You can see the M code in the file attached.
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You
12 Replies
could you pls provide some sample data and the expected output?
Hi Anonymous ,
You can add an index column before un-pivoting your data.
This will prevent your plotting of weeks from getting duplicated.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqlHQhQAFGAvGwKSVYnVAGhwLCnJSFRSA4q6JxSVghqGBAoQ2gtKmYJpcDU6JeUAIEg9PhWoAywNpMyhtoUCuhlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"| Product | Region | Week1 | Week2 | Week3 |" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"| Product | Region | Week1 | Week2 | Week3 |", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "| Product | Region | Week1 | Week2 | Week3 |", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"| Product | Region | Week1 | Week2 | Week3 |.1", "| Product | Region | Week1 | Week2 | Week3 |.2", "| Product | Region | Week1 | Week2 | Week3 |.3", "| Product | Region | Week1 | Week2 | Week3 |.4", "| Product | Region | Week1 | Week2 | Week3 |.5", "| Product | Region | Week1 | Week2 | Week3 |.6", "| Product | Region | Week1 | Week2 | Week3 |.7"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"| Product | Region | Week1 | Week2 | Week3 |.1", type text}, {"| Product | Region | Week1 | Week2 | Week3 |.2", type text}, {"| Product | Region | Week1 | Week2 | Week3 |.3", type text}, {"| Product | Region | Week1 | Week2 | Week3 |.4", type text}, {"| Product | Region | Week1 | Week2 | Week3 |.5", type text}, {"| Product | Region | Week1 | Week2 | Week3 |.6", type text}, {"| Product | Region | Week1 | Week2 | Week3 |.7", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"| Product | Region | Week1 | Week2 | Week3 |.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"| Product | Region | Week1 | Week2 | Week3 |.2", "Product"}, {"| Product | Region | Week1 | Week2 | Week3 |.3", "Region"}, {"| Product | Region | Week1 | Week2 | Week3 |.4", "Week1"}, {"| Product | Region | Week1 | Week2 | Week3 |.5", "Week2"}, {"| Product | Region | Week1 | Week2 | Week3 |.6", "Week3"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"| Product | Region | Week1 | Week2 | Week3 |.7"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns1",1),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Top Rows",{{"Week1", Int64.Type}, {"Week2", Int64.Type}, {"Week3", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type2", "Index", 0, 1, Int64.Type),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Product", "Region", "Index"}, "Attribute", "Value")
in
#"Unpivoted Columns"
You can see the M code in the file attached.
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank YouHi Anonymous ,
Greetings!
In the file which I have provided the solution, you can see that I have added an index column,
then unpivoted the week columns.
This index helps maintain row uniqueness when you need to pivot or identify rows later.
This needs to be done before transposing.
Since I didnt had any context of your data, I re-produced the scenario with a sample data.
1.Sample data
2. Before transposing-
add an index column then use week columns to un-pivot your data.
3. You will the following result.The code shared earlier is an M code.
You can refer this file attached to get more clarity.
Since you didnt respond after following up, we thought the issue was resolved.
Apologies for the confusion.
If you have a different data, please share it so that we could assist you better.
Please feel free to raise a seperate ticket in case on any questions.
Thanks for your cooperation.
Hi Anonymous ,
If possible, could you please provide more details about your data?How to provide sample data in the Power BI Forum
You can refer the following link to upload the file to the community.
How to upload PBI in CommunityThank you.
Hi Anonymous ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank YouHi @John_Doe3 ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank YouHi @John_Doe3 ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You