none
Result of Power Query operation not showing rows where value is blank RRS feed

  • Question

  • Hi,

    In this file - http://sdrv.ms/13YOSbM, the sheet called Desired result has been created from the Source data sheet (via some method other than Power Query).  This result is absolutely correct.  Another sheet called source data adjusted has been created from the Source data sheet (by merely concatenating some columns and top 2 rows - the data remains unchanged).

    If you filter data on the desired result sheet on the last column with no blanks, the number of rows is 6,167 - these are the exact number of rows returned by the Power Query tool in sheet3 - so far so good.  The total number of rows (no filter) on Desired result sheet is 23,587 whereas on sheet3, it is 6,167 - so the Power Query result is not showing rows where the value is blank.  How do I get the Power Query result in sheet3 to show all 23.587 rows.

    Thank you.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Wednesday, August 28, 2013 11:13 AM

Answers

  • Hi Ashish,

    You can find the reference to this function in the "Power Query Library Specification" PDF document.

    BTW - As Theresa was describing, you don't need to manually type the formula if you don't want to. You can right-click on the column where you want to replace the values and find "Replace Values" in the context menu. This will bring up a dialog where you can introduce the Old Value and Value to replace. Additionally, you can specify whether to only replace the Old Value when it matches the full cell value, or even if it is only a portion of the value.

    Thanks,
    M.

    Saturday, September 7, 2013 8:09 PM

All replies

  • Hi Ashish,

    Reading through your description a couple of times and trying it out in the workbook you provided, I believe the issue is caused by using unpivot. Currently, the unpivot function only provides rows for values in the columns which means the null values are not in the result. As a workaround, you could replace the null values with text (something like "null"), unpivot, and then replace the values back to null. However, this would also have to be done for any future columns that are added.  I'll also investigate this on our side to better understand how we could improve the function for your scenario.

    Please let us know if you have any other issues!

    Thanks,

    Theresa

    Wednesday, August 28, 2013 11:56 PM
    Owner
  • Hi,

    Thank you for replying.  So, before unpivoting is there a way to replace all Null cells in the Power Query window with some text entry.  Once the data is unpivoted, the text entry can be replace with Null.

    Please note that I would not like to do any replacement in the original data on the Excel sheet.

    Please help.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Thursday, August 29, 2013 12:02 AM
  • Hi,

    Any ideas please.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Monday, September 2, 2013 11:57 PM
  • Hi Ashish,

    Try using Table.ReplaceValue which takes a table, the old value, the new value and the columns to search. Unfortunately, as I mentioned early, you'll have to specify new columns to search as they are added to the data source. More information about the function can be found in the Library spec. Let us know if you have any other questions!

    Thanks,

    Theresa

    Wednesday, September 4, 2013 1:25 AM
    Owner
  • Hi,

    Thank you for replying.  I searched for the Table.ReplaceValue syntax but could not find it anywhere on the net.  I even searched for it in the "Power Query Formula Language Specification (July 2013)" PDF document but there was no mention of this.

    This is my existing query

    let
        Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
        Unpivot = Table.Unpivot(Source,{"1AALJ05 :11.00-20   BT 112   18 PR", "1AALJ07 :12.00-20  KAALAPATTHAR  18 PR", "1AALJ08 :11.00-20  KAALAPATTHAR  18 PR", "1AALK02 :12.00-20 BT 111", "1AARJ03 :12.00-20   BT 333   18 PR", "1AARJ04 :11.00-20   BT 334   18 PR", "1ABLH02 :10.00-20   BT 112   16 PR", "1ABLH04 :10.00-20   SAMSON LUG   16 PR", "1ABLH10 :10.00-20   BT MAX   16 PR", "1ABLH11 :10.00-20   BT ULTRA   16 PR", "1ABLH12 :10.00-20   ZING   16 PR", "1ABLH13 :10.20(16PR) BT 112 PLATINUM", "1ABLH14 :10.00-20   BT 112 *   16 PR", "1ABLH18 :10.00-20   TARZAN DLX   16 PR", "1ABLH26 :10.00-20   BT 112+   16 PR", "1ABLH29 :10.00-20   BT 112 GOLD   16 PR", "1ABLH31 :10.00-20   ZETA + DLX   16 PR", "1ABLH32 :10.00-20   TISON DLX   16 PR", "1ABLH34 :10.00-20   ULTRA PLUS   16 PR", "1ABLH39 :10.00-20  BT  ULTIMO DLX  16  PR", "1ABLJ01 :10.00-20   XPL   18 PR", "1ABLJ07 :10.00-20   KAALAPATTHAR   18 PR", "1ABLJ16 :10.00-20   XPL+   18 PR", "1ABRH01 :10.00-20   ROADMILER   16 PR", "1ABRH02 :10.00-20   BT 336   16 PR", "1ABRH03 :10.00-20   BT 339   16 PR", "1ABRH08 :10.00-20   BT 339 +   16 PR", "1ABRH10 :10.00-20   TIRIB DLX   16 PR", "1ABRH11 :10.00-20   BT 369 DLX   16 PR", "1ABRH12 :10.00-20   BT 339*   16 PR", "1ABRH13 :10.20  BT 339  PLATINUM(16PR)", "1ABRH15 :10.00-20  ZYNO DLX   16 PR", "1ACLH02 :9.00-20   BT 112   16 PR", "1ACLH04 :9.00-20   KALAPATTHAR   16 PR", "1ACLJ01 :9.00-20   SAMSON   18 PR", "1ACRG01 :9.00-20   BT 334*  14 PR", "1ACRH02 :9.00-20   SUPERBT   16 PR", "1ACRH05 :9.00-20   BT 339+  16 PR", "1ACRH08 :9.00-20   BT 339*   16 PR", "1ACSH01 :9.00-20   RUSTOM   16 PR", "1ADLH01 :8.25-20   BT 111   16 PR", "1ADLH02 :8.25-20   BT 112   16 PR", "1ADLH03 :8.25-20  TISON  DLX  16  PR", "1ADRH01 :8.25-20   BT 336   16 PR", "1ADRH02 :8.25-20   ROADMILER   16 PR", "1ADRH03 :8.25-20  TIRIB  DLX  16  PR", "1BELH01 :8.25-16   BT 111   16 PR", "1BELH03 :7.50-16   SAMSON   16 PR", "1BELH04 :7.50-16   BT 112   16 PR", "1BELJ01 :8.25-16   XPL   18 PR", "1BELJ03 :7.50-16   XPL   18 PR", "1BERE01 :F78-15   BT 339   10 PR", "1BERF01 :7.00-15   BT 333   12 PR", "1BERG01 :7.00-16   BT 339   14 PR", "1BERH01 :8.25-16   ROADMILER   16 PR", "1BERH03 :7.50-16   BT 333   16 PR", "1BERH04 :7.50-16   BT 369   16 PR", "1BERH05 :7.50-16   ROADMILER   16 PR", "1BERH07 :8.25-16  BT  339+  16  PR", "1BFTD01 :6.00-16   SHAAN   8  PR", "1BFTD05 :7.50-16   SHAAN   8  PR", "1BFTD06 :6.50-20   SHAAN   8  PR", "1BFTF05 :12.5/80 - 18  F-TRAC  12  PR", "1BFTH01 :9.00-16   CHAKRA   16 PR", "1BGTC02 :5.90-15   BT 446   6  PR", "1BGTC03 :5.90-15   BT 444   6  PR", "1BGTC04 :5.65-12   BT 444   6  PR", "1BGTD01 :6.00-16   THUNDER   8  PR", "1BGTD04 :4.50-12   BT 444 ULT   8 PR", "1BGTD05 :165/80  D  12   BT 339  8  PR", "1BHTZ04 :165   SR   15   MILANO", "1BHTZ05 :215/75   R   15   DAZZLER", "1BHTZ06 :7.00   R   15   STRLLAR", "1BHTZ28 :195/70   R   14   DELTA", "1BHTZ34 :205/65   R   15   VECTOR", "1BHTZ35 :185/85   R   16   BETA", "1BLTC01 :5.00-19   BT 665   6  PR", "1BLTE01 :8.00-19   BT 665   10  PR", "1BLTE02 :7.00-19   BT 665   10  PR", "1CKTF03 :12.4-28   SHAAN+  12 PR", "1CKTF05 :13.6-28   SHAAN+  12  PR", "1CKTF08 :16.9-28   SHAAN+  12  PR", "1CKTF09 :14.9-28   SHAAN+  12  PR", "1CKTF10 :12.4-28   SUPER+   12  PR", "1CKTF11 :13.6-28   SUPER+   12  PR", "1DJTF04 :12.5/80 - 18  FTRAC  12  PR", "1FALH01 :1100 R 20  D 311  VICTUS", "1FARH01 :1100 R 22.5  S 311  VICTUS", "1FARH02 :1100 R 20  S 311  VICTUS", "1FASH01 :1200 R 20  A 511  VICTUS", "1FASH02 :1100 R 20  A 511  VICTUS", "1FBLH01 :1000 R 20  D 311  VICTUS", "1FBLH02 :1000 R 20  D 331  VICTUS", "1FBLH03 :1000 R 20  D 321  VICTUS", "1FBLH04 :1000 R 20  D 341  VICTUS", "1FBLH05 :1000 R 20  T 211  VICTUS", "1FBLH06 :1000 R 20  A 611  VICTUS", "1FBLH13 :1000 R 20  D 311*  VICTUS", "1FBRH01 :1000 R 20  S 311  VICTUS", "1FBSH01 :1000 R 20  A 511  VICTUS", "1FBSH02 :1000 R 20  A 521  VICTUS", "1FCRH01 :900 R 20  S 311  VICTUS", "1FCSG01 :900 R 20  A 511   VICTUS"},"Attribute","Value"),
        SplitColumnDelimiter = Table.SplitColumn(Unpivot,"Attribute",Splitter.SplitTextByDelimiter(":"),2),
        RenamedColumns = Table.RenameColumns(SplitColumnDelimiter,{{"Attribute.1", "IB_Code"}, {"Attribute.2", "SKU"}})
    in
        RenamedColumns

    What would be the exact syntax of Table.ReplaceValue line (which is to be inserted before the Unpivot line)

    Thank you for your help.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Thursday, September 5, 2013 11:39 PM
  • Hi Ashish,

    You can actually generate this function in the UX, by right clicking on a column and selecting "Replace Values...". For more information on the actual function, Table.ReplaceValue is in section 11.6.21 in the July Library specifiction (an excerpt from the document is included below). Function help is also included in the product, typing " = Table.ReplaceValue" into the formula bar will provide the function signature, a brief description, and some examples.

    For your scenario, I would suggest using null as the oldValue and "null" or another unique token as the newValue in the function. You could then replace the token back to null after the unpivot. Please let us know if you have any other issues.

    Thanks,

    Theresa

    Table.ReplaceValue

    Replaces oldValue with newValue in specific columns of a table, using the provided replacer function, such as text.Replace or Value.Replace.

    Table.ReplaceValue(table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as {Text}) as table 

     

     

    Friday, September 6, 2013 6:05 PM
    Owner
  • Hi,

    In the "Power Query Formula Language Specification (July 2013)" PDF document there is no 11.6.21 section (Section 11 ends ad 11.2.2).  In the Find box of the PDF document, I typed Table.ReplaceValue but nothing was returned.

    Next, I typed =Table.ReplaceValue in the formula bar of Power Query but no tooltip/signature appeared there.

    Can you please write the exact Table.ReplaceValue line for me.  I do not understand what replacer as function and columnsto search is?

    Please write the exact line.

    Thank you.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Friday, September 6, 2013 11:43 PM
  • Hi Ashish,

    You can find the reference to this function in the "Power Query Library Specification" PDF document.

    BTW - As Theresa was describing, you don't need to manually type the formula if you don't want to. You can right-click on the column where you want to replace the values and find "Replace Values" in the context menu. This will bring up a dialog where you can introduce the Old Value and Value to replace. Additionally, you can specify whether to only replace the Old Value when it matches the full cell value, or even if it is only a portion of the value.

    Thanks,
    M.

    Saturday, September 7, 2013 8:09 PM
  • Hi,

    In the file link which I have earlier, please try this and you will appreciate my concern

    1. Select data from range A6:DE235 of the Source Data adjusted sheet
    2. Click on From Table in the Power Query window
    3. My objective is the replace the null in all columns from column 7 onwards (a total of 103 columns)
    4. First of all, when I right click on the 7th column, there is no option there to Replace Value
    6. The option to Replace value appears when I right click on any column from 1 to 6.
    7. When I right click on column 6 and select replace value, then the following line is visible in the formula editor

    =ReplacedValue = Table.ReplaceValue(Source,"abs","dery",Replacer.ReplaceText,{"CFA NAME"})

    As you can see, this will happen only for the column named CFA Name (6th column).  I want this to be done for all columns from column 7 onwards (a total of 103 columns).

    Hope this clearly explains my issue.

    Thank you for your help.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Monday, September 9, 2013 2:02 AM