How to Fill Data for Missing Periods/ Transactions

[Anonymous]

Hi , I have taken the below data by applying Left Join from sql (Table A Left Join Table B on PeriodSNW vs PeriodECR). I am getting null values against period 202011 (Ref: Table A) for all columns , since there is no transaction done for that period. But i want data as highligted (Red text) against period 202011. I need the data in this format to create a Power BI chart , like CPLE vs CPActual@PlanRateECR , Period wise (X Axis). How to achieve it in Power BI or in SQL.

PeriodSNW(Table A)		ProjectIDSNW (Table B)	CYLESNW (Table B)	PeriodECR (Table B)	CPActual@PlanRateECR (Table B)	CPLE (Table B)
202001		PRJ0011249	20428796.8	202001	1730.700369	1702.399733
202002		PRJ0011249	20428796.8	202002	1092.647637	1702.399733
202003		PRJ0011249	20428796.8	202003	2123.677849	1702.399733
202004		PRJ0011249	20428796.8	202004	792.675242	1702.399733
202005		PRJ0011249	20428796.8	202005	467.7653983	1702.399733
202006		PRJ0011249	20428796.8	202006	2395.149289	1702.399733
202007		PRJ0011249	20428796.8	202007	571.2756721	1702.399733
202008		PRJ0011249	20428796.8	202008	4500.900816	1702.399733
202009		PRJ0011249	20428796.8	202009	2359.972407	1702.399733
202010		PRJ0011249	20428796.8	202010	3212.353026	1702.399733
202011		PRJ0011249	20428796.8	202011	0	1702.399733
202012		PRJ0011249	20428796.8	202012	2648.212539	1702.399733
202101		PRJ0011249	20428796.8	202101	123.5755711	1702.399733

[lbendlin · Accepted solution]

Do the join in Power Query and then replace null values with whatever you need.

[Anonymous · Accepted solution]

Hi @Anonymous,

So you mean you want to use the previous value to fill the following zero part in power bi?
If that is the case, you can use SQL connector with t-sql query to generate the corresponding table and replace the 0 part to null. Then you can use the 'fill down' feature to use the previous value to fill the blank records.

SQL Server connector 

How to Fill Down Blank or Null Cells in Power Query 
Regards,

Xiaoxin Sheng

[lbendlin · Accepted solution]

Do the join in Power Query and then replace null values with whatever you need.