Design patter: Manual adjustment of data

[TristanKuesters]

Hello,

this is more a generic question where I try to get an idee how this can work.

I'm working with MS Dynamics NAV (ERP solution) as the main data source.

In a perfect world every data source is well designed and all the information are stored in each field of each row.

BUT in the normal world users forget to type in information - like a "Contract no" in a purchase invoice.

To follow this example:

When the user then post the purchase invoice in the system, the system generates general ledger entries.

These entries are the base for the reports in PowerBI.

So, several general ledger entries missed data in the column "Contract no" and I can't solve the issue in the base systems (NAV) because it is not allowed to change gl entries afterwards.

So: How can I solve this data-cleaning-issue? In PowerBI or with another soltution?

To be clear, there is no way to get this information from another source - somebody have to deside manualy which value belongs to the empty fields.

I don't think it is a unique issue, but I didn't find a generic solution or design pattern about this.

Best regards,

Tristan

[ankitpatira]

@TristanKuesters Ideally power bi is a reporting solution so ETL, data cleansing should be done outside of it but then with Power BI ou get tools like power query that you can use to do your data cleansing. It really depends on you whether you want to leverage Power Query tool within power bi desktop and do your data cleansing or do it outside but to answer your question if you want to do it with power bi then you have to use power bi desktop.

[TristanKuesters]

Hm... I'm sure if I understand your answer correct.

Even when I'm trying to use Power Query language inside of PowerBI (within the query editor) I have asked myself how to deal with this issue. Because I see no solution where a user can change the data manualy (!) inside the query editor.

Next to this, I think about to create a Power BI reports whichs shows the wrong rows of a table and also export the data into an excel file. So the user can adjust in excel the missing information. And at the end, another powerbi report combine the excelfile with the correct values with the original datasource..

Do you think, this is the right way to do it? Or are there other possibilities?

[v-qiuyu-msft]

Hi @TristanKuesters,

Actually, Power BI desktop doesn't provide a feature for us to modify retrieved field data like in Excel directly. As you miss some data in the Contract no column, you can use below work around to add missed values:

Way1:

a. In Query Editor, add a Index column or custom column in the original table. 

b. Click Enter Data to add a column "Contract no" contains all data.

c. Also add a same index column or custom column to this entered table.

d. Merge original table and this new table based on index column or custom column 

Way2:

a. In Query Editor, select all table columns and right click on one column, check Copy.

b. Paste copied table in Excel, add missed data in Contract no column manually.

c. Create a new report use desktop, retrieve data from this Excel file.

Best Regards,

Qiuyun Yu

[TristanKuesters · Accepted solution]

Thats fine for me.

I will do it this way:

Create a custom IndexColumn in the table where I missed the data.

Create an excelsheet where there this IndexColumn also exist + the column with the missing data.

Import the excelsheet back in PowerBI and make a relationship between these two tables.

When calculating measures, I will add an ifempty-Statement which then "looks" at the related excel table for the contract no.

Thanks all for your suggestion.