power query - data cleaning and analysis

SergeiBaklan
Diamond Contributor
Oct 22, 2024
noushad1ali2000

forgot csv
Source.csv1 KB
- noushad1ali2000
  Copper Contributor
  Oct 22, 2024
  SergeiBaklan Appreciated, it is really same what i am looking for
  
  Thanks a lot
  
  could you please elaborate little more or could you please suggest some video related to this topic
  
  Thanks once again ..
  - SergeiBaklan
    Diamond Contributor
    Oct 23, 2024
    noushad1ali2000
    
    First we remove the row with date of extraction
    
    and split the rest on the tables having blocks of 15 rows each
    
    Each table in this list is transformed by function, results are combined.
    
    What function do is first renames columns with needed information in the table
    
    , remove all other columns, replace spaces and empty strings on null. Next transform table to columns, in each column remove nulls and combine table back from such columns.

Diamond Contributor

Oct 22, 2024

noushad1ali2000

With some assumptions regarding the logic in source data, and keeping the source in csv file, main query could be

let
    BlockSize = 15,
    Source = Csv.Document(File.Contents("C:\Test\Source.csv"),[Delimiter=",", Columns=15, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    RemoveDate = Table.Skip(Source,1),
    SplitSource = Table.Split( RemoveDate, BlockSize),
    Custom1 = Table.Combine( List.Transform( SplitSource, (t) => fnTransformID(t) ) )
in
    Custom1

which calls the function

(tbl as table) =>

let
    RenameColumns = Table.RenameColumns(
        tbl,
        {
              {"Column2", "Code"}
            , {"Column3", "Grade"}
            , {"Column5", "ID"}
            , {"Column9", "Title"}
            , {"Column13", "Role"}
        }),
    names = {"ID", "Role", "Grade", "Code", "Title"},
    SelectColumns = Table.SelectColumns(RenameColumns,names),
    ReplaceSpace = Table.ReplaceValue(SelectColumns," ",null,Replacer.ReplaceValue,names),
    ReplaceEmpty = Table.ReplaceValue(ReplaceSpace,"",null,Replacer.ReplaceValue,names),
    ReplaceRoleWord = Table.ReplaceValue(ReplaceEmpty,"role",null,Replacer.ReplaceValue,{"Title"}),
    ToColumns = Table.ToColumns( ReplaceRoleWord ),
    RemoveNulls = List.Transform( ToColumns, (q) => List.RemoveNulls(q)),
    GetTable = Table.FromColumns( RemoveNulls, names )
in
    GetTable

Result is