none
Create new tables RRS feed

  • Question

  • Hi ,

    I am new in power bi and  I have a problem creating 2 tables.

    I have used query editor to edit my excel data into a clean data table. I would like to create 2 ta new tables based on the the first edited clean table of about 50000 rows. (example)

    Date           Status      Value

    2018-11      ok            4.3

    2018-12      Error        error message

    2018-15      ok           8.5

    The 2 new tables will be constructed based on the data of 1 of the columns of the clean table. This column with header Status has only 2 values:  OK  and Error. 

    The first table should contain all the rows that have "Error" in the Status column

    The second table should contain  all the rows that have "ok" on the status column. The third column will have decimal data if ok or  text data("error message") if error.

    What is the right easiest way to create this 2 tables based on the first???

    My try:

    I have tried to: 1. filter the Status column(clean table), first with only values of "ok" and then  create a new table using Reference function.

    2. After, I modified the filter of the Status column with only values of "error" and create a second table also using the reference function

    Problem: in this way I have several problems with several errors that I cant identify. After I Apply&Close the query editor the 2 tables have errors since the third column value sometimes has decimal format and text format depending if it is ok or error.

    Also, it seems that in the 2 new tables the format was change to only decimal or only text

    Question:

    1.Is there any easier way to create these 2 new tables based on the first edited table???

    2. Should I used duplicate instead of reference?

    3. How can I approach this  issue taking in account that the third column will have 2 different types of data ( decimal or text) depending if it is error or ok??

    thank you and regards

    Wednesday, November 28, 2018 8:44 PM

Answers

  • 1.Is there any easier way to create these 2 new tables based on the first edited table???

    The first table should be the source for the two other tables. If there is a changed type step in the first table, remove it.

    2. Should I used duplicate instead of reference?

    No. Each new table should reference the first table. Then filter each accordingly.

    3. How can I approach this  issue taking in account that the third column will have 2 different types of data ( decimal or text) depending if it is error or ok??

    The first table should have a connection only (not returned to Excel as a table). Since the two new tables will have only one type of data in the Value column(number or text), you should not have any problems. Also, you could explicitly set the type for the Value column in each table.

    Wednesday, November 28, 2018 9:28 PM