Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to Fill Data for Missing Periods/ Transactions

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 PRJ001124920428796.82020011730.7003691702.399733
202002 PRJ001124920428796.82020021092.6476371702.399733
202003 PRJ001124920428796.82020032123.6778491702.399733
202004 PRJ001124920428796.8202004792.6752421702.399733
202005 PRJ001124920428796.8202005467.76539831702.399733
202006 PRJ001124920428796.82020062395.1492891702.399733
202007 PRJ001124920428796.8202007571.27567211702.399733
202008 PRJ001124920428796.82020084500.9008161702.399733
202009 PRJ001124920428796.82020092359.9724071702.399733
202010 PRJ001124920428796.82020103212.3530261702.399733
202011 PRJ001124920428796.820201101702.399733
202012 PRJ001124920428796.82020122648.2125391702.399733
202101 PRJ001124920428796.8202101123.57557111702.399733
2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

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

View solution in original post

v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
lbendlin
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.