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
kk_Domme_kk_Aap
Frequent Visitor

i have an ask? (help)

hello fellow Powerbians, i am so very confusion rn.

so this goofy ahh database im working with has the hour and minutes as for example 71,88 so this should be 71 hours and 52 minutes. i want to show this as 71 hours and 52 minutes but it cap at 24 hour so it say for example 23:52

PowerBitch_2-1663149969455.png

 

PowerBitch_0-1663149758960.png

as you can see "gepland is the hours and minutes (minutes as 100) and custom is what i want to change it to but it caps at 24"


 i cannot change the time to duration format in my data view but in powerbottom query view it is store as duration

PowerBitch_1-1663149874744.png



i have broken multiple keyboards right now and i am running low on keyboards:


1 ACCEPTED SOLUTION

Apologies. My mistake. Try this code instead:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "NY25DcAwDAN3cc1CjyVLsxjef40EodMdyAO599AUWI2DPaYj9COVgDdRCx3EoNcJIa3rmECMWAKnHvPfaGG3GnmnFpVGOQNP2D0vvNJ5AA==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Gepland = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Gepland", type number}}),
  #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Gepland", "Gepland - Copy"),
  #"Multiplied Column" = Table.TransformColumns(
    #"Duplicated Column",
    {{"Gepland - Copy", each _ * 100, type number}}
  ),
  #"Extracted Last Characters" = Table.TransformColumns(
    #"Multiplied Column",
    {{"Gepland - Copy", each Text.End(Text.From(_, "es-ES"), 2), type text}}
  ),
  #"Changed Type1" = Table.TransformColumnTypes(
    #"Extracted Last Characters",
    {{"Gepland - Copy", Int64.Type}}
  ),
  #"Divided Column" = Table.TransformColumns(
    #"Changed Type1",
    {{"Gepland - Copy", each _ / 100, type number}}
  ),
  #"Multiplied Column1" = Table.TransformColumns(
    #"Divided Column",
    {{"Gepland - Copy", each _ * 60, type number}}
  ),
  #"Rounded Off" = Table.TransformColumns(
    #"Multiplied Column1",
    {{"Gepland - Copy", each Number.Round(_, 0), type number}}
  ),
  #"Changed Type2" = Table.TransformColumnTypes(#"Rounded Off", {{"Gepland - Copy", type text}}),
  #"Added Conditional Column" = Table.AddColumn(
    #"Changed Type2",
    "Custom",
    each if [#"Gepland - Copy"] = "0" then "00" else [#"Gepland - Copy"]
  ),
  #"Inserted Merged Column" = Table.AddColumn(
    #"Added Conditional Column",
    "Merged",
    each Text.Combine({"0", [#"Gepland - Copy"]}),
    type text
  ),
  #"Changed Type3" = Table.TransformColumnTypes(
    #"Inserted Merged Column",
    {{"Gepland - Copy", Int64.Type}}
  ),
  #"Added Conditional Column1" = Table.AddColumn(
    #"Changed Type3",
    "Custom.1",
    each if [#"Gepland - Copy"] < 10 then [Merged] else [Custom]
  ),
  #"Inserted Text Before Delimiter" = Table.AddColumn(
    #"Added Conditional Column1",
    "Text Before Delimiter",
    each Text.BeforeDelimiter(Text.From([Gepland], "es-ES"), ","),
    type text
  ),
  #"Inserted Merged Column1" = Table.AddColumn(
    #"Inserted Text Before Delimiter",
    "Final",
    each Text.Combine({[Text Before Delimiter], ":", [Custom.1]}),
    type text
  ),
  #"Removed Columns" = Table.RemoveColumns(
    #"Inserted Merged Column1",
    {"Gepland - Copy", "Custom", "Merged", "Custom.1", "Text Before Delimiter"}
  )
in
  #"Removed Columns"

time.png

 

Something I din't mention is that the minutes are rounded to the nearest minute!





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

7 REPLIES 7
PaulDBrown
Community Champion
Community Champion

You cannot have a Time field with hours > 24h formatted as hh:mm (It will convert it to a max of 24h). You can however have it as text:

 

 

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "NY25DcAwDAN3cc1CjyVLsxjef40EodMdyAO599AUWI2DPaYj9COVgDdRCx3EoNcJIa3rmECMWAKnHvPfaGG3GnmnFpVGOQNP2Ht+Hg==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Gepland = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Gepland", type number}}),
  #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Gepland", "Gepland - Copy"),
  #"Split Column by Delimiter" = Table.SplitColumn(
    Table.TransformColumnTypes(#"Duplicated Column", {{"Gepland - Copy", type text}}, "es-ES"),
    "Gepland - Copy",
    Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
    {"Gepland - Copy.1", "Gepland - Copy.2"}
  ),
  #"Changed Type1" = Table.TransformColumnTypes(
    #"Split Column by Delimiter",
    {{"Gepland - Copy.1", Int64.Type}, {"Gepland - Copy.2", Int64.Type}}
  ),
  #"Divided Column" = Table.TransformColumns(
    #"Changed Type1",
    {{"Gepland - Copy.2", each _ / 100, type number}}
  ),
  #"Multiplied Column" = Table.TransformColumns(
    #"Divided Column",
    {{"Gepland - Copy.2", each _ * 60, type number}}
  ),
  #"Rounded Off" = Table.TransformColumns(
    #"Multiplied Column",
    {{"Gepland - Copy.2", each Number.Round(_, 0), type number}}
  ),
  #"Replaced Value" = Table.ReplaceValue(
    #"Rounded Off",
    null,
    0,
    Replacer.ReplaceValue,
    {"Gepland - Copy.2"}
  ),
  #"Inserted Merged Column" = Table.AddColumn(
    #"Replaced Value",
    "Merged",
    each Text.Combine({"0", Text.From([#"Gepland - Copy.2"], "es-ES")}),
    type text
  ),
  #"Added Conditional Column" = Table.AddColumn(
    #"Inserted Merged Column",
    "Custom",
    each if [#"Gepland - Copy.2"] < 10 then [Merged] else [#"Gepland - Copy.2"]
  ),
  #"Changed Type2" = Table.TransformColumnTypes(
    #"Added Conditional Column",
    {{"Custom", type text}}
  ),
  #"Inserted Merged Column1" = Table.AddColumn(
    #"Changed Type2",
    "Final",
    each Text.Combine({Text.From([#"Gepland - Copy.1"], "es-ES"), ":", [Custom]}),
    type text
  ),
  #"Removed Columns" = Table.RemoveColumns(
    #"Inserted Merged Column1",
    {"Gepland - Copy.1", "Gepland - Copy.2", "Merged", "Custom"}
  )
in
  #"Removed Columns"

 

 

time.png

 If you need the numerical value, just use the original Gepland field

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






how i copy past this?

You need to add the steps onwards from 

 #"Duplicated Column"

to the query in the advanced editor. You will probably also need to rename and edit the steps references if there are previous steps with the same name.

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






i copy pasta succes but example 18,5 show minute like 03 minuut not 30 minuut

very sad rn coz i brain hurt

PowerBitch_0-1663166563251.png

 

Apologies. My mistake. Try this code instead:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "NY25DcAwDAN3cc1CjyVLsxjef40EodMdyAO599AUWI2DPaYj9COVgDdRCx3EoNcJIa3rmECMWAKnHvPfaGG3GnmnFpVGOQNP2D0vvNJ5AA==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Gepland = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Gepland", type number}}),
  #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Gepland", "Gepland - Copy"),
  #"Multiplied Column" = Table.TransformColumns(
    #"Duplicated Column",
    {{"Gepland - Copy", each _ * 100, type number}}
  ),
  #"Extracted Last Characters" = Table.TransformColumns(
    #"Multiplied Column",
    {{"Gepland - Copy", each Text.End(Text.From(_, "es-ES"), 2), type text}}
  ),
  #"Changed Type1" = Table.TransformColumnTypes(
    #"Extracted Last Characters",
    {{"Gepland - Copy", Int64.Type}}
  ),
  #"Divided Column" = Table.TransformColumns(
    #"Changed Type1",
    {{"Gepland - Copy", each _ / 100, type number}}
  ),
  #"Multiplied Column1" = Table.TransformColumns(
    #"Divided Column",
    {{"Gepland - Copy", each _ * 60, type number}}
  ),
  #"Rounded Off" = Table.TransformColumns(
    #"Multiplied Column1",
    {{"Gepland - Copy", each Number.Round(_, 0), type number}}
  ),
  #"Changed Type2" = Table.TransformColumnTypes(#"Rounded Off", {{"Gepland - Copy", type text}}),
  #"Added Conditional Column" = Table.AddColumn(
    #"Changed Type2",
    "Custom",
    each if [#"Gepland - Copy"] = "0" then "00" else [#"Gepland - Copy"]
  ),
  #"Inserted Merged Column" = Table.AddColumn(
    #"Added Conditional Column",
    "Merged",
    each Text.Combine({"0", [#"Gepland - Copy"]}),
    type text
  ),
  #"Changed Type3" = Table.TransformColumnTypes(
    #"Inserted Merged Column",
    {{"Gepland - Copy", Int64.Type}}
  ),
  #"Added Conditional Column1" = Table.AddColumn(
    #"Changed Type3",
    "Custom.1",
    each if [#"Gepland - Copy"] < 10 then [Merged] else [Custom]
  ),
  #"Inserted Text Before Delimiter" = Table.AddColumn(
    #"Added Conditional Column1",
    "Text Before Delimiter",
    each Text.BeforeDelimiter(Text.From([Gepland], "es-ES"), ","),
    type text
  ),
  #"Inserted Merged Column1" = Table.AddColumn(
    #"Inserted Text Before Delimiter",
    "Final",
    each Text.Combine({[Text Before Delimiter], ":", [Custom.1]}),
    type text
  ),
  #"Removed Columns" = Table.RemoveColumns(
    #"Inserted Merged Column1",
    {"Gepland - Copy", "Custom", "Merged", "Custom.1", "Text Before Delimiter"}
  )
in
  #"Removed Columns"

time.png

 

Something I din't mention is that the minutes are rounded to the nearest minute!





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Thank you so much for the help PaulDBrown, I had been stuck on it for a while 🙂

kk_Domme_kk_Aap
Frequent Visitor

😞

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.