cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Eman Frequent Visitor
Frequent Visitor

Function to convert bytes into KB MB GB TB PB , etc

This is my first time using Power BI:

 

I'm trying to turn data sizes in Bytes into readable KB (divided by 1024), MB (divided by 1048576) and down the line... I'm trying it several different ways and just can't seem to make Power BI happy with my code. I keep trying to find the right formats and from everything I've read here it seems like I should be using M Query in the Custom Column, I've also tried making it a function, which I think should be my preference since I have multiple columns and also want to be able to perform the conversion on items I'm displaying in my reports.

 

As a function:

 

  ToFileSize = (size) => 
    size = size.ToNumber(size, "F"),
    string = 
    if size >= 1125899906842624 then Text.Format("#{0} PB" , (size / 1125899906842624).ToText("F") ),
      else if size >= 1099511627776 then Text.Format("#{0} TB" , (size / 1099511627776).ToText("F") ),
        else if size >= 1073741824 then = Text.Format("#{0} GB" , (size / 1073741824).ToText("F") ),
          else if size >= 1048576 then Text.Format("#{0} MB" , (size / 1048576).ToText("F") ),
            else if size >= 1024 then Text.Format("#{0} KB" , (size / 1024).ToText("F") ) ,
              else size.Format("#{0} B" , size )
in
	#ToFileSize 

as an if statement inside Cusom Column

if [Used Space] > 1024 and [Used Space] < 1048576 then " KB" else " B"
if [Used Space] > 1048576 and [Used Space] < 1073741824 then " MB" else " B"
if [Used Space] > 1073741824 and [Used Space] < 1099511627776 then " GB" else " B"

Trying different formats and styles of code

if (([Used Space] > 1024) and ([Used Space] < 1048576) ) then,
Text.Format(([Used Space], "#,##0.00, KB") else Text.Format(([Used Space], "#,##0.00 B")

Does it want IF(logical_test>,<value_if_true>, value_if_false) or 

In excel, I could just provide this string in the custom number format and it would do the rest:

[<1024]"#0.0#, KB";[<1048576]"#0.0#,, MB";[<1073741824]"#0.0#,,, GB";[<1099511627776]"#0.0#,,,, TB";[<1125899906842624]"#0.0#,,,,, PB"

I'm trying to get away from my complex, do it all in one column and just write a basic if statement, and I can't get it to accept even this:

if [Used Space] > 1024 and [Used Space] < 1048576 then
  " KB" 
else 
  " B"
if [Used Space] > 1048576 and [Used Space] < 1073741824 then
  " MB"
else 
  " B"
if [Used Space] > 1073741824 and [Used Space] < 1099511627776 then
  " GB"
else
  " B"

I looked at the formatting in the M query docs and that follows it exactly(I think), but won't work.

 

I also tried a switch, because I see that exists in DAX, but I'm not sure where I can use DAX, if I can?

 

DAX(SWITCH(TRUE()
([Used Space] < 1024), Format([Used Space], "#0.0#, MB"),
([Used Space] < 1048576), Format([Used Space], "#0.0#,, GB"),
([Used Space] < 1073741824), Format([Used Space], "#0.0#,, TB"),
([Used Space] < 1099511627776), Format([Used Space], "#0.0#,, PB")
))

Worth a shot?

 

So, I know I'm way off base, but I can't seem to get even a simple function to work in syntax or function, no matter how many examples I've read over the last few days.

 

Any help is much appreciated!

 

Here's some sample data:

 

server3,100DB09A-FEEF-4DA2-AC23-AA10A2D62494,c:\1,3,17678991360,991682560,19685396480,NULL,16993792000,36679188480,16002109440,19000197120,00:00.0,00:00.0,name,0B18DB57-FD1E-4046-BEA3-1CD76505C2C9,01:04.0,NULL,0
server4,AFD2FE6F-DCD9-449B-9FFD-D93F63DE5FE7,c:\2,0,22974300160,5667045376,84049469440,NULL,16697712640,1.00747E+11,11030667264,77772881920,00:00.0,00:00.0,name,94A346BE-B1CA-497A-96D5-74927D9DBB56,03:00.1,NULL,0
server5,AC4980DF-016C-4B7D-8719-F859123470E3,c:\3,0,72267857920,5953306624,96727543808,NULL,97101729792,1.93829E+11,91148423168,1.21561E+11,00:00.0,00:00.0,name,0A9E55F3-2349-47CE-B783-00D574F8337C,47:03.7,NULL,0
server6,C6EC1BF6-ADA7-4FFC-9922-8C6C6BE3DE66,c:\4,0,1.28849E+12,8.55711E+11,4.76589E+11,NULL,1.08041E+12,1.557E+12,2.24699E+11,2.68509E+11,00:00.0,00:00.0,naem,0B18DB57-FD1E-4046-BEA3-1CD76505C2C9,06:35.8,NULL,0
server7,748EC969-5B1E-4E76-88E3-19D75BB59FD1,c:\5,0,83456163840,22234152960,1.38045E+11,NULL,33805881344,1.71851E+11,11571728384,88394956800,00:00.0,00:00.0,name,0B18DB57-FD1E-4046-BEA3-1CD76505C2C9,32:20.2,NULL,0
server8,456EC003-2B34-4771-9331-018B6BC823DA,c:\6,0,18255708160,8335544320,25735491584,NULL,30101180416,55836672000,21765636096,37580963840,00:00.0,00:00.0,SystemState\Server5,B72CE4AF-59AA-4A5A-B4E6-C0C6DD3C2A61,19:08.7,NULL,0
server9,8E4B95BD-4608-4B34-A44E-26D065570D27,c:\7,2,6.16059E+11,15698341888,2.62589E+12,NULL,5.66982E+12,8.29571E+12,5.65412E+12,7.67966E+12,00:00.0,00:00.0,Volume - Server - 01,78807814-B222-4A76-82C1-59392F726C83,59:11.7,NULL,0
server10,C78BBF95-544A-413B-A405-CFBE98E2CD67,c:\8,0,1.26951E+11,3381465088,2.41219E+11,NULL,1.54167E+11,3.95387E+11,1.50786E+11,2.68435E+11,00:00.0,00:00.0,Hyper-V - farm - 01,E123B83C-0388-46C4-85C0-E23B0399E74A,47:25.3,NULL,0
server11,7E213142-B226-41FC-9518-832CA47E8BED,c:\9,0,1.72166E+11,15692271616,3.57419E+11,NULL,1.36869E+11,4.94288E+11,1.21177E+11,3.22123E+11,00:00.0,00:00.0,SystemState\Local - 05,834DC770-936E-4E73-A3BC-229CD28D29BD,40:49.3,NULL,0
server12,F70774E4-B689-4779-A1B9-8D5CBD610499,c:\10,0,2.14748E+11,4975063040,8.54771E+11,NULL,2.79595E+12,3.65072E+12,2.79098E+12,3.43597E+12,00:00.0,00:00.0,Volume - Server - 01,78807814-B222-4A76-82C1-59392F726C83,59:11.7,NULL,0
server13,3357815E-B23F-4CBE-977C-5F7EF9C22D39,c:\11,0,1677721600,141520896,1980399616,NULL,1312129024,3292528640,1170608128,1614807040,00:00.0,00:00.0,Database - Remote - 01,829DF175-2A1E-4FB4-976D-3819ADD7F2E2,24:48.4,NULL,0
server14,3357815E-B23F-4CBE-977C-5F7EF9C22D39,c:\12,0,1677721600,163155968,1940529152,NULL,1351999488,3292528640,1188843520,1614807040,00:00.0,00:00.0,Database - Remote - 01,829DF175-2A1E-4FB4-976D-3819ADD7F2E2,24:48.4,NULL,0
server15,3357815E-B23F-4CBE-977C-5F7EF9C22D39,c:\13,0,1677721600,163708928,1957306368,NULL,1335222272,3292528640,1171513344,1614807040,00:00.0,00:00.0,Database - Remote - 01,829DF175-2A1E-4FB4-976D-3819ADD7F2E2,24:48.4,NULL,0
server16,3357815E-B23F-4CBE-977C-5F7EF9C22D39,c:\14,0,1677721600,165494784,1738366976,NULL,1554161664,3292528640,1388666880,1614807040,00:00.0,00:00.0,Database - Remote - 01,829DF175-2A1E-4FB4-976D-3819ADD7F2E2,24:48.4,NULL,0
server17,3357815E-B23F-4CBE-977C-5F7EF9C22D39,c:\15,0,1677721600,165969920,1954865152,NULL,1337663488,3292528640,1171693568,1614807040,00:00.0,00:00.0,Database - Remote - 01,829DF175-2A1E-4FB4-976D-3819ADD7F2E2,24:48.4,NULL,0

Here's my query:

let
    Source = Folder.Files("C:\Users\data"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from data", each #"Transform File from data"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from data"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from data", Table.ColumnNames(#"Transform File from data"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", Int64.Type}, {"Column5", type number}, {"Column6", type number}, {"Column7", type number}, {"Column8", type text}, {"Column9", type number}, {"Column10", type number}, {"Column11", type number}, {"Column12", type number}, {"Column13", type datetime}, {"Column14", type datetime}, {"Column15", type text}, {"Column16", type text}, {"Column17", type datetime}, {"Column18", type text}, {"Column19", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "ProductionServerName"}, {"Column2", "ReplicaId"}, {"Column3", "ReplicaPhysicalPath"}, {"Column4", "ReplicaScheduleType"}, {"Column5", "ReplicaShadowCopyAllocatedSize"}, {"Column6", "ReplicaShadowCopyUsed"}, {"Column7", "ReplicaFreeSpace"}, {"Column8", "ReplicaValidToDateTime"}, {"Column9", "ReplicaDiskUsed"}, {"Column10", "ReplicaDiskAllocated"}, {"Column11", "ReplicaUsedKb"}, {"Column12", "ReplicaAllocated"}, {"Column13", "ReplicaStartDateTime"}, {"Column14", "ReplicaEndDateTime"}, {"Column15", "PGFriendlyName"}, {"Column16", "PGID"}, {"Column17", "PGValidFromDateTime"}, {"Column18", "PGValidToDateTime"}, {"Column19", "PGIsOrphan"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"PGIsOrphan"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"ReplicaScheduleType", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "ReplicaScheduleType", "ReplicaScheduleType - Copy"),
    #"Removed Columns1" = Table.RemoveColumns(#"Duplicated Column",{"ReplicaScheduleType - Copy"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Columns1", "ReplicaScheduleTypeFriendly", each if [ReplicaScheduleType] = 0 then "weekly" else if [ReplicaScheduleType] = 1 then "monthly" else if [ReplicaScheduleType] = 2 then "quarterly" else if [ReplicaScheduleType] = 3 then "yearly" else null, type text),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Conditional Column",{"PGValidToDateTime"}),
    #"Inserted Age" = Table.AddColumn(#"Removed Columns2", "ReplicaDuration", each Date.From([ReplicaEndDateTime]) - Date.From([ReplicaStartDateTime])),
    #"Reordered Columns" = Table.ReorderColumns(#"Inserted Age",{"Source.Name", "ProductionServerName", "ReplicaId", "ReplicaPhysicalPath", "ReplicaScheduleTypeFriendly", "ReplicaScheduleType", "ReplicaShadowCopyAllocatedSize", "ReplicaShadowCopyUsed", "ReplicaFreeSpace", "ReplicaValidToDateTime", "ReplicaDiskUsed", "ReplicaDiskAllocated", "ReplicaUsedKb", "ReplicaAllocated", "ReplicaStartDateTime", "ReplicaEndDateTime", "PGFriendlyName", "PGID", "PGValidFromDateTime", "ReplicaDuration"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns",{{"ReplicaAllocated", "ReplicaAllocatedKb"}, {"ReplicaDiskAllocated", "ReplicaDiskAllocatedKb"}, {"ReplicaDiskUsed", "ReplicaDiskUsedKb"}}),
    #"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns2",{"ReplicaValidToDateTime"}),
    #"Renamed Columns3" = Table.RenameColumns(#"Removed Columns3",{{"ReplicaAllocatedKb", "ReplicaAllocated"}, {"ReplicaDiskAllocatedKb", "ReplicaDiskAllocated"}, {"ReplicaDiskUsedKb", "ReplicaDiskUsed"}, {"ReplicaUsedKb", "ReplicaUsed"}}),
    #"Removed Columns4" = Table.RemoveColumns(#"Renamed Columns3",{"ReplicaScheduleType"}),
    #"Renamed Columns4" = Table.RenameColumns(#"Removed Columns4",{{"PGFriendlyName", "Protection Group"}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns4",{"Source.Name", "ProductionServerName", "Protection Group", "PGID", "ReplicaId", "ReplicaPhysicalPath", "ReplicaScheduleTypeFriendly", "ReplicaShadowCopyAllocatedSize", "ReplicaShadowCopyUsed", "ReplicaFreeSpace", "ReplicaDiskUsed", "ReplicaDiskAllocated", "ReplicaUsed", "ReplicaAllocated", "ReplicaStartDateTime", "ReplicaEndDateTime", "PGValidFromDateTime", "ReplicaDuration"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns1",{{"ReplicaId", Order.Ascending}}),
    #"Reordered Columns2" = Table.ReorderColumns(#"Sorted Rows",{"Source.Name", "ProductionServerName", "Protection Group", "PGID", "ReplicaPhysicalPath", "ReplicaId", "ReplicaScheduleTypeFriendly", "ReplicaShadowCopyAllocatedSize", "ReplicaShadowCopyUsed", "ReplicaFreeSpace", "ReplicaDiskUsed", "ReplicaDiskAllocated", "ReplicaUsed", "ReplicaAllocated", "ReplicaStartDateTime", "ReplicaEndDateTime", "PGValidFromDateTime", "ReplicaDuration"}),
    #"Renamed Columns5" = Table.RenameColumns(#"Reordered Columns2",{{"ReplicaPhysicalPath", "Replica"}, {"ReplicaScheduleTypeFriendly", "Replica Schedule"}}),
    #"Removed Columns5" = Table.RemoveColumns(#"Renamed Columns5",{"ReplicaShadowCopyAllocatedSize", "ReplicaShadowCopyUsed", "ReplicaDiskUsed", "ReplicaDiskAllocated"}),
    #"Renamed Columns6" = Table.RenameColumns(#"Removed Columns5",{{"ReplicaFreeSpace", "Free Space"}, {"ReplicaUsed", "Used Space"}, {"ReplicaAllocated", "Space Allocated"}}),
    #"Reordered Columns3" = Table.ReorderColumns(#"Renamed Columns6",{"Source.Name", "ProductionServerName", "Protection Group", "PGID", "Replica", "ReplicaId", "Replica Schedule", "Used Space", "Free Space", "Space Allocated", "ReplicaStartDateTime", "ReplicaEndDateTime", "PGValidFromDateTime", "ReplicaDuration"}),
    #"Renamed Columns7" = Table.RenameColumns(#"Reordered Columns3",{{"ReplicaStartDateTime", "Start DateTime"}, {"ReplicaEndDateTime", "End Date Time"}}),
    #"Removed Columns6" = Table.RemoveColumns(#"Renamed Columns7",{"PGValidFromDateTime"}),
    #"Sorted Rows1" = Table.Sort(#"Removed Columns6",{{"ProductionServerName", Order.Ascending}, {"ReplicaId", Order.Ascending}, {"Start DateTime", Order.Ascending}}),
    #"Removed Columns7" = Table.RemoveColumns(#"Sorted Rows1",{"Source.Name"}),
    #"Renamed Columns8" = Table.RenameColumns(#"Removed Columns7",{{"ProductionServerName", "ServerName"}, {"Replica Schedule", "Schedule Type"}}),
    #"Sorted Rows2" = Table.Sort(#"Renamed Columns8",{{"ServerName", Order.Ascending}, {"Protection Group", Order.Ascending}, {"ReplicaId", Order.Ascending}, {"Start DateTime", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows2", "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Renamed Columns9" = Table.RenameColumns(#"Added Index1",{{"Index.1", "Previous"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns9", "Custom", each if [Used Space] < 1024 then Text.Combine({Number.ToText([Used Space]), "B"}, " ") else
if [Used Space] < 1048576 then Text.Combine({Number.ToText(Number.Round([Used Space]/1024, 2)), "KB"}, " ") else 
if [Used Space] < 1073741824 then Text.Combine({Number.ToText(Number.Round([Used Space]/1048576, 2)), "MB"}, " ")
else Text.Combine({Number.ToText(Number.Round([Used Space]/1073741824, 2)), "GB"}, " ")),
    #"Removed Columns8" = Table.RemoveColumns(#"Added Custom",{"Custom"})
in
    #"Removed Columns8"
3 REPLIES 3
NickNg278 Senior Member
Senior Member

Re: Function to convert bytes into KB MB GB TB PB , etc

@Eman,

If you are using M query, try this :

if [Used Space] < 1024 then Text.Combine({Number.ToText([Used Space]), "B"}, " ") else
if [Used Space] < 1048576 then Text.Combine({Number.ToText(Number.Round([Used Space]/1024, 2)), "KB"}, " ") else 
if [Used Space] < 1073741824 then Text.Combine({Number.ToText(Number.Round([Used Space]/1048576, 2)), "MB"}, " ")
else Text.Combine({Number.ToText(Number.Round([Used Space]/1073741824, 2)), "GB"}, " ")

 If using DAX (Preferred way), create a measure like this:

Total Used Space =
VAR total =
    SUM ( Table1[Used Space] ) + 0
RETURN
    IF (
        total < 1024,
        FORMAT ( total, "#0.0# B" ),
        IF (
            total < POWER ( 2, 20 ),
            FORMAT ( total / POWER ( 2, 10 ), "#0.0# KB" ),
            IF (
                total < POWER ( 2, 30 ),
                FORMAT ( total / POWER ( 2, 20 ), "#0.0# MB" ),
                FORMAT ( total / POWER ( 2, 30 ), "#0.0# GB" )
            )
        )
    )
Eman Frequent Visitor
Frequent Visitor

Re: Function to convert bytes into KB MB GB TB PB , etc

@NickNg278,

There's something off about both of those, but I want to do this the right way, so let's go forward with DAX.

 

I added my m query and some sample data to my original post. The size values are in E-L in the original data. They're listed there in bytes, so I'm going to need to come up with a way to get bigger in size.

 

Dax as a custom column returned the same data for all of the rows.

ttelrocj Occasional Visitor
Occasional Visitor

Re: Function to convert bytes into KB MB GB TB PB , etc

Sweet, Nick. Worked like a champ.