i have an ask? (help)

[Anonymous]

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

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

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

[PaulDBrown]

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"

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

[Anonymous]

how i copy past this?

[PaulDBrown]

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.

[Anonymous]

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

very sad rn coz i brain hurt

[PaulDBrown · 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"

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

[Anonymous]

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

[Anonymous]

😞