Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Regd multi row input file

Dear Team,

 

I have an issue with the input file. . It is xlsx format. My first 3 rows of the file points shows the first record. Please see this table below. Emplid column is present only once but he has 3 different jobs. How do I transform this data set? Please note there are multiple rows like this. Thanks in advance. 

 

Emplid IDEmpl NameJobsjoining datelocation
123john ConorDeveloper1-1-1990Canada
  

QA

1-1-1990Canada
  BA1-1-1990Canada
Need Help

1 Reply

  • Mahesh0016's avatar
    Mahesh0016
    Super User
    3 years ago

    Anonymous 
    > step 1 : if blank value then replace blank value with null.

     

     


    > step 2 : Select Both column >then right Click > Fill > Down
    >fill Down side column.

     

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRysrPyFNwzs/LLwJyXFLLUnPyC1JBbENdILS0NAAynRPzElMSlWJ1opWAPCAKdCSgwAm3AkMjE6i19LAwFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Emplid ID" = _t, #"Empl Name" = _t, Jobs = _t, #"joining date" = _t, location = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Emplid ID", Int64.Type}, {"Empl Name", type text}, {"Jobs", type text}, {"joining date", type date}, {"location", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Emplid ID"}),
    #"Replaced Value" = Table.ReplaceValue(#"Filled Down","",null,Replacer.ReplaceValue,{"Empl Name"}),
    #"Filled Down1" = Table.FillDown(#"Replaced Value",{"Empl Name"})
in
    #"Filled Down1"

     

     

     

    Anonymous i hope this helps you! Thank You!!