Forum Discussion
Anonymous
4 years agoNot applicable
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...
- 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!
PaulDBrown
Community Champion
4 years agoApologies. 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
4 years agoNot applicable
Thank you so much for the help PaulDBrown, I had been stuck on it for a while 🙂