2

The picture I have attached shows what my power query table looks like (exactly the same as source file) and then underneath what I would like the final end product to look like. Correct me if I'm wrong but I thought the purpose of power query/power bi was to not manipulate the source file but do this in power query/power bi?
If that's the case, how can I enter new columns and data to the existing table below?

ORIGINAL TABLE VS END PRODUCT

sldonovan77
  • 79
  • 1
  • 2
  • 10

2 Answers2

1

You can add custom columns without manipulating source file in power bi. Please refer to below link.

https://learn.microsoft.com/en-us/power-bi/desktop-add-custom-column

EDIT: Based on your comment editing my answer - Not sure if this helps.

Click on edit queries after loading source file to power bi.

Using 'Enter Data' button entered sample data you provided and created new table. Data can be copy pasted from excel. You can enter new rows manually. Using Tag number column to keep reference. enter image description here

Merge Queries - Once the above table is created merged it with original table on tag number column.

enter image description here

Expand Table - In the original table expand the merged table. Uncheck tag number(as it is already present) and uncheck use original column name as prefix. enter image description here

Now the table will look like the way you wanted it.

enter image description here

You can always change data(add new columns/rows) manually in new table by clicking on gear button next to source. enter image description here

srinivasu
  • 51
  • 4
  • 1
    Thanks for the reply srinivasu. I should have clarified that I'm trying to enter data MANUALLY therefore this is more complicated than adding a column. Can you help me with this? – sldonovan77 Apr 15 '20 at 08:46
  • Hi, is the answer that provind help? With this query you can create an empty column and specify the value of each row without issues. – Tomas Michel Apr 15 '20 at 17:40
  • 1
    Hi Srinivasu - I'll give the above a go...I think this should work!! Fingers crossed and thanks again all for helping. I'll let you know asap – sldonovan77 Apr 16 '20 at 02:05
  • 1
    Hi srinivasu - this worked perfectly!! Thanks very much for your help with this. Greatly appreciated!! :-) – sldonovan77 Apr 16 '20 at 02:39
0

Here is the closest solution to what I found from "manual data entry" letting you as much freedom as you would like to add rows of data, if the columns that you want to create do not follow a specific pattern.

I used an example for the column "Mob". I have not exactly reproduced the content of your cells but I hope that this will not be an issue to understand the logic.

Here is the data I am starting with:

enter image description here

Here is the Power Query in which I "manually" add a row:

  #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Mob", each if [Tag Number] = "v" then null else null),
    NewRows = Table.InsertRows(#"Added Conditional Column", 2, {[Mob="15-OHIO", Tag Number="4353654", Electronic ID=1.5, NLIS="", Date="31/05/2015", Live Weight="6", Draft="", Condition store="", Weighing Type="WEAN"]})
in
    NewRows

1) I first created a column with only null values:

  #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Mob", each if [Tag Number] = "v" then null else null),

2) With the "Table.InsertRows" function:

  • I indicated the specific line: 2, (knowing that power Bi start counting at zero, at the "headers" so it will the third line in the file)

  • I indicated the column at which I wanted to insert the value, i.e "Mob"

  • I indicated the value that all other other rows should have:

      NewRows = Table.InsertRows(#"Added Conditional Column", 2, {[Mob="15-OHIO", Tag Number="4353654", Electronic ID=1.5, NLIS="", Date="31/05/2015", Live Weight="6", Draft="", Condition store="", Weighing Type="WEAN"]})

Here is the result: enter image description here

I hope this helps. You can apply this logic for all the other rows.

I do not think that this is very scalable however, becaue you have to indicate each time the values of the rows in the other columns as well. There might be a better option.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tomas Michel
  • 146
  • 1
  • 3
  • 17
  • 1
    Hi Tomas, thankyou very much for all of your input. I've come up with some sort of error. I probably haven't explained myself well enough. I think I may need to work out another way of doing this. – sldonovan77 Apr 16 '20 at 02:03
  • Do not hesitate to share your steps in the advandced editor so that we can fix it. Keep in mind that I did what I did with a reduced sample of your data and that you need to adapt it. – Tomas Michel Apr 16 '20 at 05:38