Forum Discussion
Errors when plotting week numbers
- 1 year ago
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 - Anonymous1 year ago
.... Wait.... What?
"Just add an index column"
followed by:
enormous gigantic something something code?
It still says I have double values
After much research, I have found that my sheet contained another set of data behind my original set. In other words, there was a 2nd dataset next to my 1st dataset. After removing this second dataset, the error disappeared.
Now I'm left with another problem where my weeknumbers are not in order, and my graphs are all skewed... Either way, it would appear that this particular solution did in fact work, and the problem was on my end. My apologies. Now it's on to figure out problem #85,756