HELP TO GRAPH multiple data

[ipso]

I very kindly request information, for the following inconvenience
HELP FOR GRAPHING MULTIPLE DATA IN ONE CEL

 

I have 

try to separate the columns by character / and it only brings me in the graph the first data found in the first column.

how could i graph this?

ty for your help

[Ashish_Mathur]

Hi,

In the Query Editor, split the second column by rows with / as a delimiter.  Now create your desired visual.

[ipso]

THANKS FOR YOUR TIME.

I have to do the analisys of that data and i have a problem...

One of those cells have multiple data in one cell for example

 ID             DATA

  X             A / B / C / D

  Y             C / D

I need know how many have each ID in ESPECIFIC data and get that in a table:

       A   B   C   D      TOTAL

X      1   1  1    1         4

Y                 1    1       2

                                  6

I tried to do that with split column but the table just give me the first data found in the first column

     A   B  C  D  TOTAL

X    1                   1

Y              1         1

                            2

I managed to do the exercise by making a table by column, but I need the data in a single table like this:

       A   B   C   D      TOTAL

X      1   1  1    1         4

Y                 1    1       2

                                  6

[HenriqueReis]

Hi!

First open the query editor in here:

So, you're going to have this:

Right-click on the top of the column, then go to "split column" and "by delimiter":

So, you gonna see this screen:

The final result you gonna have:

Return to Power BI with a left click here:

Regards!
https://www.youtube.com/channel/UC9nTjkTNOhnQxHj57tj45lw

  

[ipso]

Hola, hablas español?

[Arul]

@ipso ,

please provide clear info on your actual and expected output.

[ipso]

HELLO. ty for your time...

i have to do the analisys of that data and i have a problem...

One of those cells have multiple data in one cell for example

 ID             DATA

  X             A / B / C / D

  Y             C / D

I need know how many have each ID in ESPECIFIC data and get that in a table:

       A   B   C   D      TOTAL

X      1   1  1    1         4

Y                 1    1       2

                                  6

I tried to do that with split column but the table just give me the first data found in the first column

     A   B  C  D  TOTAL

X    1                   1

Y              1         1

                            2

I managed to do the exercise by making a table by column, but I need the data in a single table like this:

       A   B   C   D      TOTAL

X      1   1  1    1         4

Y                 1    1       2

                                  6

[mahenkj2]

Hi @ipso ,

In query editor, let's start with base data (sample):

Then split column as below:

that's it.

Then close and apply the changes of query editor.

Now u can use this table data in matrix visual to get exact same format data you intend to make:

I did this with a visual, and the table created is long format. I think this is fine, if you really want to take advantage of dimensional design, by later relating a dim table with DATA fields.

Pls let me know if your desired outpt must be in the table only.

Hope it helps.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[ipso]

ASHISH, TAHNKS a lot for your time

it´s not so simple becose i have more than 1000 dates. 

as you can see here i divided them:

when sorted them into the array I need, it brings me only the ones from the first column,

these would be the values placed in the array

I could get a table for each separation but you need all the data in the same table

so I made this table separating each of the variables to take it and cross it with the data

Here it generates the first error

As you can see, an error was generated crossing the information, but in addition to that when I try to make the table, the "pqrs classifications" do not appear in the table.

I appreciate your help BLESSINGS

[Ashish_Mathur]

I do not understand.  My reply definitely solved your initial question.  This seems to be a new requirement now.  Furthermore since all information is in Spanish, I cannot understand your requirement either.

[ipso]

here: grafico.pbix 

that is the data with I´m testing.  thanks a lot

[PaulDBrown]

Following @Ashish_Mathur  advice to split the column by delimeters into rows, subsitute the code in the advanced editor for the worksheet with the following:

let
    Origen = Excel.Workbook(File.Contents("D:\Users\pc\Downloads\ReportePQRS_220515.xlsx"), null, true),
    Worksheet_Sheet = Origen{[Item="Worksheet",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Worksheet_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Id", Int64.Type}, {"Caso", Int64.Type}, {"ID Tecnico", Int64.Type}, {"Nombre Tecnico", type text}, {"Folder", type text}, {"Fecha Recibido", type datetime}, {"Medio Comunicación", type text}, {"Fecha Peticionario", type datetime}, {"Asunto", type text}, {"email", type text}, {"Remitente", type text}, {"Area Remitente", type text}, {"Tipo PQRS", type text}, {"Area PQRS", type text}, {"Clasificación PQRS", type text}, {"Dane Municipio", Int64.Type}, {"Municipio", type text}, {"Dane I.E.", type text}, {"Nombre I.E.", type text}, {"Dane Sede(s)", type text}, {"Nombre Sede(s)", type text}, {"Segmento", Int64.Type}, {"Operador", type text}, {"Contrato", type text}, {"Tipo Ración", type text}, {"Nº TDD Afectados", Int64.Type}, {"Contenido Petición", type text}, {"Fecha Alcance", type datetime}, {"Descripción Alcance", type text}, {"Nº Radicado", type any}, {"Cod. descuento", type any}, {"Requiere mejora", type any}, {"Observación mejora", type any}, {"Requiere Seguimiento", type any}, {"Observación seguimiento", type any}, {"Evidencia Registro", type text}, {"Evidencia Alcance", type text}, {"Gestiones", type text}, {"Estado", Int64.Type}, {"Fecha registro", type datetime}, {"Fecha modificación", type datetime}, {"Clasificación PQRS - Copia.1", type text}, {"Clasificación PQRS - Copia.2", type text}, {"Clasificación PQRS - Copia.3", type text}, {"Clasificación PQRS - Copia.4", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Clasificación PQRS", Splitter.SplitTextByDelimiter(" / ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Clasificación PQRS"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Clasificación PQRS", type text}})
in
    #"Changed Type1"

I've attached the file for you

[ipso]

Hello, friend, i´ve been learning how to integrate the code that you gave me. is it ok?:

let
Origen = Excel.Workbook(File.Contents("D:\Users\pc\Downloads\ReportePQRS_220515.xlsx"), null, true),
Worksheet_Sheet = Origen{[Item="Worksheet",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Worksheet_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Id", Int64.Type}, {"Caso", Int64.Type}, {"ID Tecnico", Int64.Type}, {"Nombre Tecnico", type text}, {"Folder", type text}, {"Fecha Recibido", type datetime}, {"Medio Comunicación", type text}, {"Fecha Peticionario", type datetime}, {"Asunto", type text}, {"email", type text}, {"Remitente", type text}, {"Area Remitente", type text}, {"Tipo PQRS", type text}, {"Area PQRS", type text}, {"Clasificación PQRS", type text}, {"Dane Municipio", Int64.Type}, {"Municipio", type text}, {"Dane I.E.", type text}, {"Nombre I.E.", type text}, {"Dane Sede(s)", type text}, {"Nombre Sede(s)", type text}, {"Segmento", Int64.Type}, {"Operador", type text}, {"Contrato", type text}, {"Tipo Ración", type text}, {"Nº TDD Afectados", Int64.Type}, {"Contenido Petición", type text}, {"Fecha Alcance", type datetime}, {"Descripción Alcance", type text}, {"Nº Radicado", type any}, {"Cod. descuento", type any}, {"Requiere mejora", type any}, {"Observación mejora", type any}, {"Requiere Seguimiento", type any}, {"Observación seguimiento", type any}, {"Evidencia Registro", type text}, {"Evidencia Alcance", type text}, {"Gestiones", type text}, {"Estado", Int64.Type}, {"Fecha registro", type datetime}, {"Fecha modificación", type datetime}, {"Clasificación PQRS - Copia.1", type text}, {"Clasificación PQRS - Copia.2", type text}, {"Clasificación PQRS - Copia.3", type text}, {"Clasificación PQRS - Copia.4", type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Clasificación PQRS", Splitter.SplitTextByDelimiter(" / ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Clasificación PQRS"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Clasificación PQRS", type text}})
in
#"Changed Type1"

I cant runed it

[ipso]

Hello, thanks for your time. i can´t see on the attachet, the process, could you show me some pictures about the process.

thank you so much

[PaulDBrown · Accepted solution]

Step by step...

Select the column and, under the Home tab,  click on Split column -> by delimiter

Type in the delimeter in the box, and in advanced options chooe split into rows.

Finally select the column and under the Transform tab, select Format and Trim and then Clean.