Forum Discussion

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