Forum Discussion

SridharDasari's avatar
SridharDasari
Copper Contributor
Sep 13, 2020

Hi Riny_van_Eekelen ,

 

I have got that JSON data by using an API, nothing more than that.

 

Thanks for your solution, the solution you provided is exactly what i needed. But, i need to know why you copied the columns A & B to columns E & F. Why don't you do on the columns i have given.

 

I have tried in original columns it is not working. Will you please elaborate how can i proceed with that. Let me know if any issues if we do on the same columns.

 

Thanks

Sridhar Dasari

 

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Sep 13, 2020

SridharDasari When I worked on my initial answer I had some error messages form the original data. Can't remember exactly what. Didn't have much time so decided to just copy it to get a clean start, assuming you would be able to adopt my query to your own file.

 

Now, I had a chance to apply the same steps to your original data, called "Table5".

  • SridharDasari's avatar
    SridharDasari
    Copper Contributor
    Sep 13, 2020

    Thanks Riny_van_Eekelen 

     

    Will you please update the steps that you have done now. Because query looks same as original.

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1_2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CommitmentID", type text}, {"CommitmentName", type text}, {"Type", type text}, {"Hours", Int64.Type}, {"CommittedCost", Int64.Type}, {"ActualCost", Int64.Type}, {"OpenCommitment", Int64.Type}})
    in
    #"Changed Type"

     

    In the above code it is showing "Table1_2" not "Table5"(Highlighted). So please give me the steps what you done.

     

    Thanks

    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Sep 13, 2020

      SridharDasari "Table1_2" is the name of the table that gets loaded back into Excel. If you want to change the data types, just add a step at the end of the query that generates the output table. Done that in the attached file.

       

      In summar,y all that the query does is:

       

      1. Connect to the data table called "Table5"

      2. Trim and clean both columns to get rid of trailing and/or leading spaces and possible other "strange" characters

      3. Add an index column and filter out rows that have no data ("null") in Column1

      4. Pivot Column1 with values from from Column2 (Don't aggregate in Advance options)

      5. Fill up the six last columns

      6. Filter "null" from the CommentID column

      7. Remove the index

      8. Change data types

       

      Close and load to a table in Excel.

       

       Does this answer your questions?

Resources