shorten 12 columns into 7 required columns using Power Query

[alipezu]

Dear members,

Please help me to shorten the 12 columns data on Sheet1 into 7 columns data shown on Sheet2 using the Power Query.

Regards.

Here is the link to my workbook = https://drive.google.com/file/d/0B9uKuwzDH_rQWVByNmpQbDI1dzA/view?usp=sharing

https://drive.google.com/file/d/0B9uKuwzDH_rQWVByNmpQbDI1dzA/view?usp=sharing

 

[billszysz]

https://drive.google.com/open?id=0B6UlMk8OzUrxaUpuZER5Y3haN2M

 

[Useful]

I am grateful to both of you.
I wanted to learn a technique how to apply a power query so I provided a sample table with just 12 columns but now here I have an actual table with too many columns which I want to apply a query on. I applied what you guys guided me in the previous replies but I didn’t get the correct results.
I don’t want to take up your time but I would like to guide me once again.
Here is my new workbook: https://drive.google.com/file/d/0B9uKuwzDH_rQRnNfcEMzRmJGNGs/view?usp=sharing
Thanks!

of cource it shouldn't work the reason is your new table headers isn't same as before

try this

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type2" = Table.TransformColumnTypes(Source,{{"Driver Name", type text}, {"Driver ID", type any}, {"Driver Mobile", type text}, {"Driver Mobile2", Int64.Type}, {"Total Trips", Int64.Type}, {"Designation", type text}, {"Work Area", type text}, {"Bus Number", type text}, {"Bus Brand", type text}, {"Capacity", Int64.Type}, {"Bus Type", type text}, {"Kindergarten", type text}, {"residence", type any}, {"Total Students", Int64.Type}, {"points", type any}, {"start date", type any}, {"Boys School", type text}, {"residence1", type text}, {"Total Students4", Int64.Type}, {"points5", type any}, {"start date5", type datetime}, {"Girls School Cycle1", type text}, {"residence.", type text}, {"Total Students.", Int64.Type}, {"points2", type any}, {"Start Date2", type any}, {"Girls School Cycle2", type text}, {"residence2", type text}, {"Total Students2", Int64.Type}, {"Points3", type any}, {"Start Date3", type any}, {"Girls School Cycle3", type text}, {"Residence3", type text}, {"Total Students3", Int64.Type}, {"points4", type any}, {"Start Date4", type any}, {"Assistant", type any}, {"Assistant ID", type any}, {"Mobile", type any}, {"Assistant Boys", type any}, {"Assistant ID2", type any}, {"Mobile2", type any}, {"Assistant Girls1", type any}, {"Assistant ID3", type any}, {"Mobile3", type any}, {"Assistant Girls2", type any}, {"Assistant ID4", type any}, {"Mobile4", type any}, {"Assistant Girls3", type any}, {"Assistant ID5", type any}, {"Mobile5", type any}, {"Column1", type any}, {"#1", type text}, {"#2", type text}, {"#3", Int64.Type}, {"#4", type text}, {"#5", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type2",{"Driver Name", "Driver ID", "Bus Number", "Capacity", "Kindergarten", "Total Students", "Boys School", "Total Students4", "Girls School Cycle1", "Total Students.", "Girls School Cycle2", "Total Students2", "Girls School Cycle3", "Total Students3"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Other Columns",{{"Total Students", "Kindergarten Students"}, {"Total Students4", "Boys School Students"}, {"Total Students.", "Girls School Cycle1 Students"}, {"Total Students2", "Girls School Cycle2 Students"}, {"Total Students3", "Girls School Cycle3 Students"}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns1", {"Driver Name", "Driver ID", "Bus Number", "Capacity"}, "Attribute", "Value"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Columns", "Attribute", "Attribute - Copy"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column"," Students","",Replacer.ReplaceText,{"Attribute - Copy"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Attribute - Copy", "St_Types"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Driver ID", "St_Types"}, {{"Gr", each if Table.RowCount(Table.FillDown(Table.AddColumn(_, "Fill_Down", each if Value.Is([Value], type number) then null else [Value]),{"Fill_Down"}))=2 then Table.RemoveFirstN(Table.FillDown(Table.AddColumn(_, "Fill_Down", each if Value.Is([Value], type number) then null else [Value]),{"Fill_Down"}),1) else Table.FillDown(Table.AddColumn(_, "Fill_Down", each if Value.Is([Value], type number) then null else [Value]),{"Fill_Down"})  , type table}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Driver ID"}),
    #"Expanded Gr" = Table.ExpandTableColumn(#"Removed Columns", "Gr", {"Attribute", "Bus Capacity", "Bus Number", "Driver ID", "Driver Name", "Fill_Down", "St_Types", "Value"}, {"Attribute", "Bus Capacity", "Bus Number", "Driver ID", "Driver Name", "Fill_Down", "St_Types.1", "Value"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Gr",{"St_Types.1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Driver Name", "Driver ID", "Bus Number", "Bus Capacity", "St_Types", "Fill_Down", "Value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Value", Int64.Type}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Value", null}}),
    Finish = Table.RenameColumns(#"Replaced Errors",{{"St_Types", "Student_Type"}, {"Fill_Down", "Student_Name"}, {"Value", "Total_Student"}})
in
    Finish

 

[alipezu]

Once again thank you so much to both of you bros you made my work so easy.
Now my last question is that how can I change the Sheet1 data into Sheet2 data format using Power Query or some other method?
Where the Emp Name and Emp ID must not be repeated in the rows.
Though I did it manually and took time to finish it.
here is my workbook: https://drive.google.com/file/d/0B9uKuwzDH_rQbEJ3anZSS1Jiem8/view?usp=sharing

 

[billszysz]

Try this code below.... this is first shot only.

let
    Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Serial", Int64.Type}, {"Emp ID", Int64.Type}, {"Emp Name", type text}, {"Designation", type text}, {"Document Name", type text}, {"Document number", type any}, {"Issue Date", type datetime}, {"Expiry Date", type datetime}}),
    Headers = List.Skip(Table.ColumnNames(#"Changed Type"),1),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Emp ID"}, {{"tbl", each _, type table}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Emp ID"}),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Removed Columns", "tbl", Headers, Headers),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded {0}",{"Index", "Emp ID", "Emp Name", "Designation", "Document Name", "Document number", "Issue Date", "Expiry Date"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Index", "Emp ID", "Emp Name", "Designation", "Document Name"}, "Atrybut", "Wartość"),
    #"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"Document Name", "Atrybut"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Scalone"),
    #"Replaced Value" = Table.ReplaceValue(#"Merged Columns","Document","",Replacer.ReplaceText,{"Scalone"}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Replaced Value",{{"Scalone", Text.Proper}}),
    #"Pivoted Column" = Table.Pivot(#"Capitalized Each Word", List.Distinct(#"Capitalized Each Word"[Scalone]), "Scalone", "Wartość"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Index", "Serial"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Passport Issue Date", type date}, {"Passport Expiry Date", type date}, {"Visa Issue Date", type date}, {"Visa Expiry Date", type date}, {"National Id Issue Date", type date}, {"National Id Expiry Date", type date}})
in
    #"Changed Type1"

Regards

 

[Useful]

Once again thank you so much to both of you bros you made my work so easy.
Now my last question is that how can I change the Sheet1 data into Sheet2 data format using Power Query or some other method?
Where the Emp Name and Emp ID must not be repeated in the rows.
Though I did it manually and took time to finish it.
here is my workbook: https://drive.google.com/file/d/0B9uKuwzDH_rQbEJ3anZSS1Jiem8/view?usp=sharing

Hello,
also try

https://drive.google.com/open?id=0B1-oaYEAJB2CV3llY25fTXh3Y2s

(but this thread should be start from new one)