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?
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 Community
Thank you.
No, I can't.
I just went through the trouble of making the dummy files and repeating what I did to my "real" file, and it is not throwing the error.
I've had it with this program.