POWER BI - How to add manual columns/data to existing table instead of adding columns/data to the source csv file

1
votes

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

2
powerbipowerquery
I should clarify that I want to MANUALLY add data into an existing table. - sldonovan77
the columns that you are trying to add, Weighing Type,Mob Type & Mob , Is it static or does the value change based on any other column ? - Sanchit Sood

2 Answers

2
votes

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

https://docs.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

0
votes

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.