Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Missing Data after Refresh in Service

  • 1.  Missing Data after Refresh in Service

    Top Contributor
    Posted Jul 15, 2020 04:52 PM
    Hello everyone,

    I am facing an URGENT issue regarding the refresh in service and I can't find the answer to it.

    I have published a report to the service and configured data sources for manual/scheduled refresh.

    After refreshing the data in the service some of the data goes missing.
    I updated my desktop file to check if the problem persists, but the desktop file is working alright and is showing me all the data.
    Also, some of the cells are showing partial numbers, like the 2nd column in the 3rd row which has 105,000 which is correct, but in the 2nd one it has 2,601.

    Here are the screenshots:
    As you can see in the top picture the numbers are all there, but in the bottom one you can see the empty cells, which happens after the refresh on the service. Due to the missing values, the totals go wrong.
    Picture 1
    Picture 2

    The report and the refreshes had been working fine for the past 10 days and suddenly it has started giving the issue.

    I have already deleted and re-published the report to the service, but that has NOT worked.
    I was reading about Boolean values on the service not working, but I do not have that in my measures. Also, if I did have Boolean values, it should affect all the values, but here it is affecting only certain values.

    Again, the report works fine in the desktop and is giving the desired output.


    If anyone has any solution, I'd be all ears.

    Thank you,



    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Missing Data after Refresh in Service

    Silver Contributor
    Posted Jul 16, 2020 02:34 AM
    It could be that the refresh from the Power BI service is getting different data to the data you get when you refresh from the desktop (e.g. due to different credentials or even it is seeing a different datasource for some reason). A simple way to check is to download your dataset from the Power BI site after it has been refreshed and look at the data - check things like row counts and whether columns are populated - comparing the dataset refreshed in Power BI with that refreshed in your desktop.

    ------------------------------
    Andrew Simmans
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Missing Data after Refresh in Service

    Top Contributor
    Posted Jul 16, 2020 04:22 AM
    Hi @Andrew Simmans,

    I have checked, the service and the desktop file are running on the same data source.

    I also downloaded the pbix from the service and refreshed it in the desktop and the numbers were showing as they should have.
    I published the same pbix which was downloaded from the service, to the service and did a refresh from the service, but the numbers went missing again.

    I have raised a support ticket with the MS PBI​ team and they are looking into it.

    Thank you for all the suggestions though.

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Missing Data after Refresh in Service

    Silver Contributor
    Posted Jul 16, 2020 04:57 AM
    Hi Vishesh

    Sounds like you are being sorted by MS, but just for you to be clear my suggestion was you download and do NOT refresh - so you have two copies of the file - one refreshed on your desktop, one refreshed in the Power BI service and then look at the data (click on the data icon inside the main Power BI Desktop - NOT in the Transform data) in each and compare record counts, etc.

    Andrew

    ------------------------------
    Andrew Simmans
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: Missing Data after Refresh in Service

    Top Contributor
    Posted Jul 16, 2020 06:59 AM
    Edited by Vishesh Jain Jul 16, 2020 07:00 AM
    Hi @Andrew Simmans,

    I did what you asked and all the row counts are matching. I am not missing any data.

    The problem with the desktop and service is the date column.
    The desktop is treating the date column as it should, but when the refresh takes place in the service, it changes the format of the date from dd/mm/yy to mm/dd/yy (I have configured all the browser and regional settings already).

    Due to this all the dates after the 12th are converting into blanks and not being counted in the calculation, because there are no more month after the 12th.
    As soon as I remove the date filter from the page, the values show up.

    So MS needs to fix this in the service as all the calculations are correct in the desktop and all the rows are accounted for, but the service is messing up the dates​.

    Again, thank you for responding.

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: Missing Data after Refresh in Service

    Silver Contributor
    Posted Jul 16, 2020 07:17 AM
    Ah yes - the dreaded dates in US v UK format - if you google this I think you mind find some solutions - if your source holds the date as text then sometimes it works to extract the "bits" (day / month / year) and then create a date using a date format which is not ambiguous - like yyyy/mm/dd. Or if you are using the Date.FromText function you can add in a culture to say you are using a UK format. See https://docs.microsoft.com/en-us/powerquery-m/date-fromtext

    ------------------------------
    Andrew Simmans
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 7.  RE: Missing Data after Refresh in Service

    Top Contributor
    Posted Jul 16, 2020 07:58 AM
    As a work around until a permanent fix is in place could you explicitly force a date format in the data model via the date format function?

    ------------------------------
    Sam Duval
    Data Quality Analyst
    Element Financial
    Indianapolis IN
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 8.  RE: Missing Data after Refresh in Service

    Top Contributor
    Posted Jul 16, 2020 08:04 AM
    Hello,

    @Andrew Simmans I am currently in the process of doing that only, converting dates into text and then changing it with the local.
    I have not tried splitting the dates but if conversion from text doesn't work, then this will be my next option.

    @Sam Duval Can you please show me how to do force a date format.

    Thank you everyone.​​

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 9.  RE: Missing Data after Refresh in Service

    Top Contributor
    Posted Jul 16, 2020 10:21 AM
    Edited by Sam Duval Jul 16, 2020 10:22 AM
    my thought was that you'd create a new column via dax and use that as your date column.

    = FORMAT('Date'[Date], "dd/mm/yyyy")


    https://docs.microsoft.com/en-us/dax/format-function-dax
    ------------------------------
    Sam Duval
    Data Quality Analyst
    Element Financial
    Indianapolis IN
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 10.  RE: Missing Data after Refresh in Service

    Top Contributor
    Posted Jul 17, 2020 02:15 AM
    Hi @Sam Duval,

    I tried to create a calculated column as well but it hasn't worked.

    I split the date into 3 parts day, month and year and the combined them in a separate column and used the FORMAT() function, but eventually I have convert the data type to date because this column is going to be linked to the calendar table in the model.

    So PBI service is screwing things up, no matter what I try.

    If anyone has any more suggestions I am all ears.

    Thank you,​​

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 11.  RE: Missing Data after Refresh in Service

    Silver Contributor
    Posted Jul 17, 2020 10:54 AM
    Vishesh,

    So I think what you are saying is that because the date is in the wrong format the column ends up as "blank" in the DAX tables when you look at the table. Is that right? Or does the date appear OK in the DAX Table? Also please confirm the date in the DAX table is marked as a date or a datetime column.

    ------------------------------
    Andrew Simmans
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 12.  RE: Missing Data after Refresh in Service

    Top Contributor
    Posted Jul 18, 2020 06:28 AM
    Hi @Andrew Simmans,

    So after quite a long investigation, I found that the format of the date column in the source itself is messed up.
    It is changing the format mid-column from ​​dd-mm-yy to mm-dd-yyyy.

    I tried to covert that column to text and then split it into day, month and year column, then I combined them back again in yyyy-mm-dd format and then changed the column type to date.

    Example:

    mm/dd/yy
    12/07/2020

    dd/mm/yy
    13/07/2020

    Now because I am reading it as text, 13/07/2020(dd/mm/yyyy) gets converted to 07/13/2020.
    Now if I split the 12/07/2020(mm/dd/yy) into month, date and year it works fine.
    But when I split 07/13/2020(dd/mm/yy) into month, date and year it gives an error, since there is no 13th month.

    Now if I try to convert the 12/07/2020 in dd/mm/yy it converts it into 7th December 2020 instead.

    So no matter what I do, if the format is going to change mid-column, there is going to be an error in the dates from where the column changes,because unlike Excel, PBI columns can have only a single data type.

    So I am stuck until this issue is resolved and yes I have a calendar table in my data model, which is marked as calendar table.

    Hope my explanation was comprehensible.

    Thank you,

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 13.  RE: Missing Data after Refresh in Service

    Top Contributor
    Posted Jul 18, 2020 09:45 AM
    Could you do two columns one for dd/mm and one for mm/dd and then combine them so if mm/dd is blank use dd/mm? Not sure how this will work if the day is 12 or less, but worth a try...

    There's a dax function that will force a given date to end of month (eomonth I think) you might be able to leverage that to at least extract a month, and then you could extract the day via process of elimination from the other field

    ------------------------------
    Sam Duval
    Data Quality Analyst
    Element Financial
    Indianapolis IN
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 14.  RE: Missing Data after Refresh in Service

    Top Contributor
    Posted Jul 20, 2020 03:11 AM
    Hi @Sam Duval,

    As you correctly pointed out, even if I do make 2 columns, eventually when I combine them with the year column, the format of that final date column will be either dd/mm/yy or mm/dd/yy so it bound to give an error either after the 12th day and also the converted dates will be wrong as I mentioned in my previous reply.

    Since we have the issue after the 12th, even pushing the date to the end of the month and extracting a month will not work for dates after 13th since there is no 13th month.

    One thing I just thought of is splitting the date into 5 columns, making a year column, 2 date columns and 2 month columns and then using the COALESCE() function to check if one of the month column is empty then use the other month column.
    Then combine them and make a date out of them.
    I am just spit balling here, but I think that (in theory) it might work and will bloat up my data model unnecessarily.

    If you or anyone else has pointers regarding this please let me know.

    Thank you,

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 15.  RE: Missing Data after Refresh in Service

    Silver Contributor
    Posted Jul 20, 2020 03:46 AM
    What is the format of the date in your original source - also what type of source is it - SQL, csv, sharepoint, etc.

    If you saying that the format of the date in your original source file / database is sometimes mm/dd/yy and sometimes is dd/mm/yy - i.e. on some records the date is 13/07/2020 and on other records it is 07/13/2020. If so then it is impossible for anyone (including a human being) to know what date 06/01/2020 is unless there is something else in your source system saying what format of date is being used. So how does your source system know what date 06/01/2020 is?



    ------------------------------
    Andrew Simmans
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 16.  RE: Missing Data after Refresh in Service

    Top Contributor
    Posted Jul 20, 2020 03:53 AM
    Hi @Andrew Simmans​,

    You are absolutely correct, that there is no way to know.

    My source is an excel file which is being extracted from a software.
    Yes the problem lies with the source, I think I mentioned it previously, but I was trying if something can be done to handle this in PBI.

    I am trying to convert the date into text, so that no matter the format, it shows dd/mm/yy, but unfortunately I have not be able to crack it yet.

    Let see, I'll keep on trying.

    Thanks for taking the time to respond.

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 17.  RE: Missing Data after Refresh in Service

    Silver Contributor
    Posted Jul 20, 2020 04:21 AM
    I think the best you are going to get is to split up the columns as you suggested earlier into 1st & 2nd, 4th & 5th and 7th through 10th. Then create a new column using

    if [1st_2nd] > 12 then #date([7th_10th],[4th_5th],[1st_2nd]) else #date([7th_10th],[1st_2nd],[4th_5th])

    the following Power Query does this with some typed in data if that helps - to use simply create a blank query and then got to advanced editor and past in:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjBU0lEyNAYSRgZGBkqxOtEQHlgcLgTmGZjBhWIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"1st_2nd" = _t, #"4th_5th" = _t, #"7th_10th" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"1st_2nd", Int64.Type}, {"4th_5th", Int64.Type}, {"7th_10th", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each if [1st_2nd] > 12 then #date([7th_10th],[4th_5th],[1st_2nd]) else #date([7th_10th],[1st_2nd],[4th_5th]))
    in
    #"Added Custom"

    ------------------------------
    Andrew Simmans
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 18.  RE: Missing Data after Refresh in Service

    Top Contributor
    Posted Jul 20, 2020 06:26 AM
    Hi @Andrew Simmans,

    Thank you for the query, I'll try it out and let you know.

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 19.  RE: Missing Data after Refresh in Service

    Top Contributor
    Posted Jul 20, 2020 09:07 AM
    Hi @Vishesh Jain

    Is your issue resolved? If not, have you tried the optional culture parameter (as @Andrew Simmans mentioned) in the Table.TransformColumnTypes step in query editor to "en-IN"? for example:

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"name", type text}, {"amt", Int64.Type}, {"Created", type datetime}},"en-IN")

    If you still have the issue, is it possible to load the sample excel and the pbix you had used to check it out?

    ​​

    ------------------------------
    Gopa Kumar
    Limner Consulting

    North Kerala User Group Leader
    https://www.pbiusergroup.com/NorthKerala
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 20.  RE: Missing Data after Refresh in Service

    Top Contributor
    Posted Jul 20, 2020 09:28 AM
    @Gopa Kumar Sivadasan his problem is the age old garbage in garbage out ​scenario, row 10 is mm/dd/yyy row 11 is dd/mm/yyyy

    ------------------------------
    Sam Duval
    Data Quality Analyst
    Element Financial
    Indianapolis IN
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 21.  RE: Missing Data after Refresh in Service

    Top Contributor
    Posted Jul 21, 2020 03:10 AM
    Hi @Gopa Kumar Sivadasan

    As correctly pointed out by @Sam Duval, after having investigated the issue, it is the garbage in garbage out scenario.

    Have a look at the pictures, I can't really share the pbix file but if the need be I'll try to share a stripped down version of it.

    ​​As you can see in the pictures, the column in changing formats after the 12th or should I say the format is correct after the 12th and this is data extracted from the system.


    I tried to convert it to text and then split it into month, date and year and then combine them, but it doesn't work because there is not 13th month.
    And yes, I tried converting the dates using locale, but again, no 13th month.

    If you have any other suggestions please let me know.

    Thank you,


    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 22.  RE: Missing Data after Refresh in Service

    Top Contributor
    Posted Jul 21, 2020 03:59 AM
    Hi @Sam Duval  thanks for the heads-up.

    Hi @Vishesh Jain 

    My point is that your source system is outputting the dates in the proper format as 12-7-2020 and 13-7-2020 are the correct date formats for the Indian system. So, I am not sure whether the issue is with your source data. It will be good if you can send the excel file and a sample pbix with your issue to have a look at it. In the meantime can you check if changing the region of your computer to US and then uploading? You can then change the date separator in your excel sheet to "/' instead of "-". This can also be done in the query editor. When you change this in excel, then it will show as 7/12/2020 and 7/13/2020 and your "mm-dd-yyyy" format should work.


    ​​

    ------------------------------
    Gopa Kumar
    Limner Consulting

    North Kerala User Group Leader
    https://www.pbiusergroup.com/NorthKerala
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 23.  RE: Missing Data after Refresh in Service

    Top Contributor
    Posted Jul 21, 2020 05:33 AM
    Hi @Gopa Kumar Sivadasan,

    I think the dates are wrong, because even though it is showing 12-7-20 due to the mm/dd/yy the date is 7th Dec(wrong) and not 12th July.
    This issue exists is a couple of more files as well. Files of other locations, which are also downloaded on the same PC are not showing this error.

    I checked the with the regional settings of the PC and everything is set to dd/mm/yy​​​​.

    My main issue is this, that after all the tampering, the dates show up correctly on PBI in my PC and even when I publish it to the Service, but if I try to refresh the same from the service, it removes the dates from the date column and replaces it with blanks. So these rows are not counted during time intelligence.

    I can't really share the pbix or the excel, but I'll try if something can be done, for you guys to help me out.

    I hope this sheds more light on the problem.

    Thank you,

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 24.  RE: Missing Data after Refresh in Service

    Silver Contributor
    Posted Jul 21, 2020 05:31 AM
    You might like to try changing the format of the "date" column in Excel to be "General" or "Number" - the "Date" is then displayed as a number and imported into Power BI as a number, but then you can force the data type to Date. See attached Excel sheet which has date in various formats (and includes an invalid date) plus the pbix it has been imported into. You will see that "invalid" dates do not get displayed as numbers in Excel and thus cause errors when imported. Hopefully all your dates will display as valid numbers and thus will import OK. Look for the "Force Columns to Dates" step in the Power Query editor and also look at the Excel spreadsheet.

    ------------------------------
    Andrew Simmans
    ------------------------------

    Attachment(s)

    pbix
    Demo.pbix   21K 1 version
    xlsx
    Book1.xlsx   9K 1 version
    Academy - Online Interactive Learning from Experts


  • 25.  RE: Missing Data after Refresh in Service

    Top Contributor
    Posted Aug 01, 2020 06:48 AM