unpivoit columns

[Zakaria_1980]

Dears,

i've made some changes under power query in power Bi service, and i got the following outcome. what i noticed is that for some KPIs i don't have for the same row and for the same KPI, both status and comment, but for the same KPI i have one row for status and another one for comment

Below what I want to be displayed in power Bi, so i can work on my visuals. whenever the row related to status and comments is empty, that row should be deleted

Thanks in advance.

[amitchandak]

@Zakaria_1980 , what was initial data?Can you share sample data and sample output in table format?

[Zakaria_1980]

how can i attach xls file for you? i don't know how to do it here please.

Thanks

[Zakaria_1980]

please find above the input and the output i need to get

thanks in advance

[Zakaria_1980]

any feedback please ?

[v-yingjl]

Hi @Zakaria_1980 ,

You can achieve this in power query editor:

1. Select [Project Name] and [Week] column to use 'Unpivot other columns'

2. Spilit column by delimiter:

3. Use this formula to add a custom column:

let z=[Attribute.1],p=[Attribute.2],
tab=
Table.Column(
Table.SelectRows(#"Changed Type1",each [Attribute.1]=z and [Attribute.2]=" Comments"
),
"Value"
)
in
if
List.Count(tab)>0 then
tab{0} else null

4. Choose row 9 and remove bottom 9 rows:

5. Remove unnecessary columns and rename other columns to get the final result:

Attached my sample file that hopes to help you, please check and try it: Unpivot columns.pbix

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Zakaria_1980]

Thanks a lot, it seems all is fine, expect the removed bottom rows. what i've shared with you is just a sample on 1 project for 1 week but in my DB i have thousands of project, weeks... i cannot count how many rows to delete from bottom knowing that my DB is refreshed and each next week will be added in the DB.

maybe we can say whenever Value column= Red or Amber or Green or Yes ==> Keep that row else Delete that row

could you share with me the new PBI report to adapt it to my DB taking into account my above hint please ?

Thanks in advance.

[v-yingjl · Accepted solution]

Hi @Zakaria_1980 ,

You can add a step after 'Added Custom' step by this formula:

= Table.SelectRows(#"Added Custom",each ([Value] = "Red" or [Value] = "Amber" or [Value] = "Green" or [Value] = "Yes"))

Then remove and rename columns to get the same result:

 

Here is the modified report, please refer the New Table in this report: unpivot columns.pbix

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[vanessafvg]

its a bit diffiult to know what you have done without seeing your code, also you say you did this in the power bi service? do you have any duplication happening?