Modify column with blanks to show the value of a previous cell (with some conditions)

[Anonymous]

Hello everyone, this is my first time posting here. I've been working for a while with PowerBI and this forum has helped me a lot, so thank you all.

I have the following problem,  I have the following table that relates diferent codes.

The numerical SAP codes have one and only one related Budget code or a Blank Budget code that should keep being Blank.
For the case of alphanumerical SAP codes the ones with the first 9 characters in common should have the same budget code.

For example, SAP codes starting with GSD-00397 should have the same Budget code (GTO-TGM-19-03).

Budget code	SAP code
GTO-TGM-19-05	000000200953
GTO-TGM-19-06	000000200968
	000000200989
GTO-TGM-19-03	GSD-00397
	GSD-00397-EPC
	GSD-00397-GRAL
	GSD-00397-MAT
GTO-TGM-19-07	GSD-00414
	GSD-00414-EPC
	GSD-00414-GRAL
	GSD-00414-MATTGN
GTO-TGM-20-02	GSD-00448
	GSD-00448-EPC
	GSD-00448-GRAL
	GSD-00448-MATTGM
GTO-RTI-18-92	000000200931

What I'm needing is to fill the budget code column with the corresponding budget code for each case.

Having that done, the example above should look like this:

Budget code	SAP code
GTO-TGM-19-05	000000200953
GTO-TGM-19-06	000000200968
	000000200989
GTO-TGM-19-03	GSD-00397
GTO-TGM-19-03	GSD-00397-EPC
GTO-TGM-19-03	GSD-00397-GRAL
GTO-TGM-19-03	GSD-00397-MAT
GTO-TGM-19-07	GSD-00414
GTO-TGM-19-07	GSD-00414-EPC
GTO-TGM-19-07	GSD-00414-GRAL
GTO-TGM-19-07	GSD-00414-MATTGN
GTO-TGM-20-02	GSD-00448
GTO-TGM-20-02	GSD-00448-EPC
GTO-TGM-20-02	GSD-00448-GRAL
GTO-TGM-20-02	GSD-00448-MATTGM
GTO-RTI-18-92	000000200931

Ps. Considering that the numerical SAP codes with no budget code should remain this way, fill down in the Query editor isn't an option.

I know that this can be done easily in Excel but I don't know how to do it in PowerBI.

Thank you in advance.

[davehus · Accepted solution]

Hi, I think this probably could be resolved within query editor. If you have a way of creating a reference table with the budget code and the SAP code. Example 

GTO-TGM-19-03

GSD-00397

From there duplicate the SAP Code column and split the "-" as far right as possible. You should be left with the GSD-00397 to match against your reference table and you can merge in the Budget Code. You should be left with a null value where you don't require it.

I've attached a file for you as an example. https://ufile.io/5r60cct9

[dkaushik]

Hi @Anonymous 

Connect your data to Power BI. 
Then click on "Transform Data" to open power query editor. 
Right click on "Budget Code" column -> Fill -> Down.

Hope this helps.

Please mark as solution if it works out 🙂

Thanks,

Dheeraj