Errors when plotting week numbers

[John_Doe3]

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?

[v-sdhruv · Accepted solution]

Hi @John_Doe3 ,
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

[v-sdhruv]

Hi @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

[v-sdhruv]

Hi @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

[v-sdhruv]

Hi @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

[v-sdhruv · Accepted solution]

Hi @John_Doe3 ,
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

[John_Doe3]

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)

[v-sdhruv]

Hi @John_Doe3 ,

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.

[John_Doe3]

It still says I have double values

[John_Doe3]

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

[John_Doe3 · Accepted solution]

.... Wait.... What?

"Just add an index column"

followed by:

enormous gigantic something something code?

[v-sdhruv]

Hi @John_Doe3 ,

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.

[John_Doe3]

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.

[ryan_mayu]

could you pls provide some sample data and the expected output?