cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ipso
Frequent Visitor

HELP TO GRAPH multiple data

 

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

 

WhatsApp Image 2022-05-13 at 9.31.18 AM.jpeg

I have 

ipso_0-1652459451015.jpeg

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

 

1 ACCEPTED SOLUTION

Step by step...

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

SplitCol.jpg

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

Split.png

 

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

TrimeClean.jpg

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

15 REPLIES 15
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Hi!

 

First open the query editor in here:

HenriqueReis_0-1652521657568.png

So, you're going to have this:

1.PNG

 

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

2.png

 

So, you gonna see this screen:

3.png

 

The final result you gonna have:

4.PNG

 

Return to Power BI with a left click here:

HenriqueReis_1-1652521946223.png

 

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

  

Hola, hablas español?

Arul
Super User
Super User

@ipso ,

please provide clear info on your actual and expected output.

Arul
ipso
Frequent Visitor

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

Hi @ipso ,

 

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

mahenkj2_0-1652719444923.png

 

Then split column as below:

mahenkj2_1-1652719494771.png

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:

 

mahenkj2_2-1652719592445.png

 

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.

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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:

ipso_0-1652683969039.png

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

ipso_1-1652684013832.png

these would be the values placed in the array

ipso_2-1652684067887.png

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

ipso_3-1652684178272.png

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

 

ipso_4-1652684215136.png

 

Here it generates the first error

 

ipso_5-1652684253025.png

 

ipso_6-1652684374638.pngipso_7-1652684385545.png

 

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ipso
Frequent Visitor

here: grafico.pbix 

 

 

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

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"

 

 

Captura de pantalla 2022-05-16 174400.png

I've attached the file for you





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






ipso
Frequent Visitor

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
Frequent Visitor

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

Step by step...

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

SplitCol.jpg

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

Split.png

 

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

TrimeClean.jpg

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors