Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Eman
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"
8 REPLIES 8
Anonymous
Not applicable

@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" )
            )
        )
    )

How can i modify this for if i am starting with KB instead of B?

If you want to use the M query for positive and negative values e.g. for changes in folder sizes make sure to replace '[Used Space] <' with 'Number.Abs([Used Space]) <'

Good stuff, however I am understandably getting an alphabetical sort instead of numerical sorting. Anyone know how I can apply custom sorting to this measure so that the sorting uses the original numeric value (i.e. Table1[Used Space])? 

 

Thanks.

Anonymous
Not applicable

I saw this article using REPT(UNICHAR(8203) and it worked for me.

https://powerbi.tips/2019/09/custom-sort-order-within-a-dax-measure/

My measure got like this: 

IF(_Result = "TB"
    , REPT(UNICHAR(8203),1) &  FORMAT ( _TotalRAM /1024, "#.0# TB" )
,IF(_Result = "GB" && _TotalRAM > 100
    , REPT(UNICHAR(8203),2) & FORMAT ( _TotalRAM, "#0.0# GB" )
,IF(_Result = "GB" && _TotalRAM > 10
    , REPT(UNICHAR(8203),3) & FORMAT ( _TotalRAM, "#0.0# GB" )
,IF(_Result = "GB" && _TotalRAM > 0
    , REPT(UNICHAR(8203),4) & FORMAT ( _TotalRAM, "#0.0# GB" )
    ,0
))))

The limitation is that we can only use it on a table or matrix. 

Here's an implementation of the sorting solution, for use in a DAX measure.   

Active = 
 VAR total =
    sum([ACTIVE_BYTES])
VAR maxlen = 20 //set maxlen larger than the possible number of values to the left of the decimal point    
 RETURN
    IF (
        total < 1024,
        REPT(UNICHAR(8203),maxlen-LEN(TRUNC(FORMAT ( total, "#0.0#" ),0))) & FORMAT ( total, "#0.0# B" ),
        IF (
            total < POWER ( 2, 20 ),
            REPT(UNICHAR(8203),maxlen-(3+LEN(TRUNC(FORMAT ( total / POWER ( 2, 10 ), "#0.0#" ),0)))) & FORMAT ( total / POWER ( 2, 10 ), "#0.0# KB" ),
            IF (
                total < POWER ( 2, 30 ),
                REPT(UNICHAR(8203),maxlen-(6+LEN(TRUNC(FORMAT ( total / POWER ( 2, 20 ), "#0.0#" ),0)))) & FORMAT ( total / POWER ( 2, 20 ), "#0.0# MB" ),
                IF (
					total < POWER ( 2, 40),
					REPT(UNICHAR(8203),maxlen-(9+LEN(TRUNC(FORMAT ( total / POWER ( 2, 30 ), "#0.0#" ),0)))) & FORMAT ( total / POWER ( 2, 30 ), "#0.0# GB" ),
					REPT(UNICHAR(8203),maxlen-(12+LEN(TRUNC(FORMAT ( total / POWER ( 2, 40 ), "#0.0#" ),0)))) & FORMAT ( total / POWER ( 2, 40 ), "#0.0# TB" )
                )                
            )
        )
    )

 

Sweet, Nick. Worked like a champ.

@Anonymous,

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.