Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 sh...
  • PaulDBrown's avatar
    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!