Forum Discussion
Errors when plotting week numbers
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.... Wait.... What?
"Just add an index column"
followed by:
enormous gigantic something something code?
I see that MadhaviT accepted something as a solution, but I think that is premature as I still have the issue (See comment after proposed solution)
Hi 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.
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