topic Push data with CSharp in Developer

Push data with CSharp

martinmatouy — Tue, 30 Jun 2020 00:35:16 GMT

Hi,

I'm working on getting my data-pushed with c# and the power bi API, my issue is with datetime columns; My explanation, in my code, when I created the dataset I'm specificating the type of my data, if the column is string, I'm sending string, when is Datetime I send DateTime, in this part my process work perfect ( O at less is what I think it is). When I'm tring to push the data, my datetime format is "yyyy-MM-ddTHH:mm:ss.fffZ" and when execute the push work fine, but in my power bi desktop the column isn't marked as datetime and when I try to work with a datetime function is telling me that cannot convert value text to date, this is the message.

I'm assuming that my data isn't in the correct format in power bi dataset, what is driving me crazy.

If someone can give me the help I'll appreciate

Thanks for the help

Re: Push data with CSharp

lbendlin — Tue, 30 Jun 2020 02:20:02 GMT

Power BI has a different type for that - it is called DateTimeZone. If it still doesn't work then you can replace the T by a space and remove the Z before converting to DateTime. (assuming that you want to keep your time as UTC)

Here's an example

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA1MNM1sgwxNLUyMLcyMtEzMDCIUoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetimezone}}) in #"Changed Type"

Re: Push data with CSharp

martinmatouy — Tue, 30 Jun 2020 13:27:39 GMT

Thanks, Ibendlin for your message, I tried removing the T between the date and the time and the Z at the end of my DateTime, but I still have the same message regarding the text type.

One thing I forgot to tell is that my data in my c sharp process, from a SQL Server DB.

I don't familiarize with the part of the code you tell me, can you explain a little more, please?

Thank again.

Re: Push data with CSharp

lbendlin — Tue, 30 Jun 2020 13:37:21 GMT

Did you try my example? Test it with one of your strings.

Re: Push data with CSharp

martinmatouy — Tue, 30 Jun 2020 13:53:02 GMT

Sorry lbendlin, I don't familiarize with that code, where should I run?

Re: Push data with CSharp

lbendlin — Tue, 30 Jun 2020 14:28:48 GMT

That is Power Query code. Run it in Excel or in Power BI Desktop.

Re: Push data with CSharp

martinmatouy — Tue, 30 Jun 2020 17:34:11 GMT

Thanks again lbendlin, when I use my Desktop version, I'm unable to select an option, it looks like disabled.

Re: Push data with CSharp

martinmatouy — Wed, 01 Jul 2020 15:59:42 GMT

Well, unfortunately, the issue was my CSharp code, I was sending the data type incorrectly.

I made the change to the data set creation process with the correct data type (Datetime) and It's working. I'm fighting now with the hierarchy of the dates fields because the streaming dataset isn't available but I have my original problem resolve.

Thank you so much for the time.

Re: Push data with CSharp

Anonymous — Mon, 13 Jul 2020 16:18:52 GMT

Hi,

I have the same issue, and I think PushDataSets have severe limitations, Data heirachy being one of them. I was never able to build a Date heirachy on the fly, and ended up creating the actual Dates in C# code.

Crappy solution, but works.

phil

Re: Push data with CSharp

martinmatouy — Mon, 13 Jul 2020 17:03:59 GMT

Thanks, Phil for your time; I agree with you in the serious limitations in the push dataset. In my case, I created in the c# the four values (year, month, day, and quarter) but seams the powerbi read as a number and when I use it the order is by number no for month. I'm questioning if the push dataset is working for me.