Post information from different columns

[abadill]

Hello good night,

Please someone who can help me with this consultation. In the following data management project information and each project has identified risks, which have been segmented into Risk Type 1, Risk Detail 1 and Risk Responsible 1. The same criterion applies when we want to record the 22 risk.

Question: How can I count these risks on a dashboard. How can I quantify the detail of those risks and their managers, since I handle several columns of information.

Best regards

Andrea

Project	RISK TYPE 1	RISK DETAIL 1	RISK 1	RISK TYPE 2	RISK DETAIL 2	RISK RISK 2
AAA	NOVEDAD_PREVENTA	MISSING SIGNED CONTRACT	With	NOVEDAD_PREVENTA	DESIGN-HLD NEWS	C and D
BBB	NOVEDAD_PLANIFICACION	DELAY IN CHRONOGRAM EXECUTION	PMO	CAMBIOS_ALCANCE	CUSTOMER REQUESTS ADJUSTMENTS TO THE SCOPE	With
CCC	NOVEDAD_DESPLIEGUE	DELAY X JOBS/CIVIL WORK CUSTOMER	Client	NOVEDAD_COMPRAS	DELAY X LACK OF QUOTE/AF	C and D

[Ashish_Mathur · Accepted solution]

Hello

The data must be transformed as shown in the image below. This M code causes the transformation

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDBasNADER/Rew5Jd8ga2Vbye7KWa3tFBNCoYUGSk75f+ptSfGh1xnNzEPL4hDR7VzSiT3665B54lSqFMVMUgcmXWIPpKlkpLI68+3x+X/Gc71+6YOHxLOtCsHb/R28u+wW1zTNNhYwSSuEJJp+sgFfQRJQnzVplzECn5nG8usPUWsfxkbUrhgIE3FVRisaOUPm08hWDNAfVi2uTAZFofQMRjrwE72yENGGZeUegnA38h/IGQ7a2J5kkgCz5iM8h+rm1+3j/tgUkMYho23SAekI2sJp1MJ7bLe/uHwD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Project = _t, #"RISK TYPE 1" = _t, #"RISK DETAIL 1" = _t, #"RISK 1" = _t, #"RISK TYPE 2" = _t, #"RISK DETAIL 2" = _t, #"RISK 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"RISK TYPE 1", type text}, {"RISK DETAIL 1", type text}, {"RISK 1", type text}, {"RISK TYPE 2", type text}, {"RISK DETAIL 2", type text}, {"RISK 2", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Attribute.2"})
in
    #"Removed Columns"

I hope this helps.

[v-lili6-msft · Accepted solution]

Hello @abadill

Ashish_Mathur pbix file works well on my side, if you could use this way to open the file on the power bi desktop

File->Open->Browse, and then choose this file.

and for your requirement, you need to depivote and pivot your table in the energy query, here is the blog, please refer to it:

https://radacad.com/pivot-and-unpivot-with-power-bi

Best regards

Lin

[Ashish_Mathur · Accepted solution]

Hello

The data must be transformed as shown in the image below. This M code causes the transformation

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDBasNADER/Rew5Jd8ga2Vbye7KWa3tFBNCoYUGSk75f+ptSfGh1xnNzEPL4hDR7VzSiT3665B54lSqFMVMUgcmXWIPpKlkpLI68+3x+X/Gc71+6YOHxLOtCsHb/R28u+wW1zTNNhYwSSuEJJp+sgFfQRJQnzVplzECn5nG8usPUWsfxkbUrhgIE3FVRisaOUPm08hWDNAfVi2uTAZFofQMRjrwE72yENGGZeUegnA38h/IGQ7a2J5kkgCz5iM8h+rm1+3j/tgUkMYho23SAekI2sJp1MJ7bLe/uHwD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Project = _t, #"RISK TYPE 1" = _t, #"RISK DETAIL 1" = _t, #"RISK 1" = _t, #"RISK TYPE 2" = _t, #"RISK DETAIL 2" = _t, #"RISK 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"RISK TYPE 1", type text}, {"RISK DETAIL 1", type text}, {"RISK 1", type text}, {"RISK TYPE 2", type text}, {"RISK DETAIL 2", type text}, {"RISK 2", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Attribute.2"})
in
    #"Removed Columns"

I hope this helps.

[abadill]

Hello

The solution that you publish is exactly what I need but when I try to pass the code I get syntax errors. Please I can pass the file, I use powerbi in Spanish. I've been making several modifications but I don't find the solution. You can pass me to my email anbadi@uees.edu.ec or if I pass it over any link no problem.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[abadill]

Hello

I can't open it when I open it since the winrar does not recognize it as a power bi file. What a pity, sorry the annoyance will be that I can sneak the step by step, how to make that change in the columns.

Thank you

Andrea.

[abadill · Accepted solution]

Thank you if it worked for me.

[v-lili6-msft · Accepted solution]

Hello @abadill

Ashish_Mathur pbix file works well on my side, if you could use this way to open the file on the power bi desktop

File->Open->Browse, and then choose this file.

and for your requirement, you need to depivote and pivot your table in the energy query, here is the blog, please refer to it:

https://radacad.com/pivot-and-unpivot-with-power-bi

Best regards

Lin

[abadill]

Thank you if I could open the file and the solution if it worked for me.

[Ashish_Mathur]

Hi,

I really do not know how else to help you.  You should just be able to download the file and on double clicking it should open in PowerBI Desktop.  I am also attaching the file for your reference.

[abadill]

I get sintaxys error. This is what I'm placing. You can share the sample file for me.

• Table.TransformColumnTypes("Promoted Headers","PROJECT", type text, "RISK TYPE 1", type text, "RISK DETAIL 1", type text, "RISK 1", type text, "RISK TYPE 2", type text, "RISK DETAIL 2", type text,
"Changed Type" ? Table.TransformColumnTypes("Promoted Headers","PROJECT", type text, "RISK TYPE 1", type text, "RISK DETAIL 1", type text, "RISK", type text, "RISK TYPE 2", type text, "RISK DETAIL 2", type text, "RISK RISK 2", type text), . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . "A" "QuoteStyle.Csv, true), "ATTRIBUTE.1", "ATTRIBUTE.2"-), "Changed Type1" - Table.TransformColumnTypes("Split Column by Delimiter","ATTRIBUTE.1", type text, "ATTRIBUTE.2", Int64.Type-),
"Pivoted Column" ? Table.Pivot("Changed Type1", List.Distinct("Changed Type1"[ATTRIBUTE.1]), "ATTRIBUTE.1", "VALUE"),
"Removed Columns" - Table.RemoveColumns("Pivoted Column","ATTRIBUTE.2"
in
"Removed Columns"