Forum Discussion

koorosh's avatar
koorosh
Post Partisan
5 years ago
Solved

Extract specific rows

Hello All, The first table represents all folders, subfolders and files along with their sizes for each user. If we want to find how much space each user used, we should extract the row just for the...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    This M code works

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSsvPSUktUtJRcraKiSktTi0qjonJzs8vyi/OAAoaGhkYKMXqAJVl5qRiVRQT4wgUN4Upw20aUCFIJW4DfTOzU2NinEC24jYMpAhkijkeC6EGgUyyACmLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, path = _t, size = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"path", type text}, {"size", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Type] = "folder")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Text.Length([path])-Text.Length(Text.Replace([path],"\",""))),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each [Custom] <= 2)
in
    #"Filtered Rows1"

    Hope this helps.

PhilipTreacy's avatar
PhilipTreacy
Super User
5 years ago

Hi koorosh 

This code does the job.  Here's a sample PBIX file with the code

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSsvPSUktUtJRcraKiSktTi0qjonJzs8vyi/OAAoaGhkYKMXqAJVl5qRiVRQT4wgUN4Upw20aUCFIJW4DfTOzU2NinEC24jYMpAhkijkeC6EGgUyyACmLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, path = _t, size = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"path", type text}, {"size", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Type] = "folder")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each if List.Count(Text.PositionOf([path],"\",3)) = 2 then [path] else null),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Custom] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Custom"})
in
    #"Removed Columns"

 

Regards

Phil

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.