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

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:

Need Help

PaulDBrown

4 years ago

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!

7 Replies

PaulDBrown

Community Champion

4 years ago

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
Not applicable
4 years ago
how i copy past this?
- PaulDBrown
  Community Champion
  4 years ago
  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
Not applicable
4 years ago
😞

Forum Discussion

i have an ask? (help)

7 Replies

Related content

Asking help for Vertical Table

need help

Calculation Help

DAX Query help

DAX Formula Help