Forum Discussion

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Sep 12, 2020

SridharDasari Not sure how you connected to the JSON data, but I copied the table in columns A and B on the "Actual Data" sheet to columns E and F. then I created a fresh query on that table and loaded the end result into Sheet1. Looks like what you asked for. I hope this is helpful and trust you will be able to figure out what I did to create the output.

 

 

  • 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

Resources