Forum Discussion

kamranmumtaz02's avatar
kamranmumtaz02
Frequent Visitor
3 years ago

Need help with power query unpivoting

Hello all,

Please check out the below screenshot of the normal table (there is an unnecessary break in each machine table) that I am getting from my colleague "DATA" along with the "DESIRED RESULT" that I want as the final result.

The data is in a regular excel table form and it would be time-taking to make official tables of each machine (a total of 16 machines) every time. I am looking forward to a code that will work if I convert the complete data into one sheet to save time by not making each machine an official excel table every time, Could anyone provide a code?

 

 

 

Need Help

4 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    Hi kamranmumtaz02 ,

    According to your description, I try my best to reproduce your problem. I create sampe like this:

    Here's my solution.

    1.Add a custom column in MACHINE1, MACHINE2, MACHINE3 tables separately like this:

    2.Select Merge Queries>Merge Queries as New.

    3.Filter out the blank and title rows.

    Get the result:

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

    • kamranmumtaz02's avatar
      kamranmumtaz02
      Frequent Visitor
      3 years ago

      @v-kal

       

      Thank you for your efforts but the provided data is sample data, there are more than 15 machines, and I cannot make them table each time, which is why I asked if is there a way to make the complete data a table and then convert it into one data. I am new to power query but can make one data from multiple sheets, but this is something challenging for me.

  • KT_Bsmart2gethe's avatar
    KT_Bsmart2gethe
    Impactful Individual
    3 years ago

    Hi kamranmumtaz02 ,

     

    I assumed the input is as below:

     

     

    let

//Import file    
Source = Excel.Workbook(File.Contents("Filepath ....."), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

//Below code is the solution:
    TableToColumns = Table.ToColumns(Sheet1_Sheet),
    SearchKeyword_Machine = List.Select(List.Transform(List.Combine(TableToColumns), Text.From), each Text.StartsWith(_, "Machine", Comparer.OrdinalIgnoreCase)),
    MachineCount = List.Count(SearchKeyword_Machine),
    RemovedNulls = List.Transform(TableToColumns, each List.RemoveNulls(_)),
    RemovedEmptyList = List.Select(RemovedNulls, each not List.IsEmpty(_)),
    ListCleanedUp = List.Transform(RemovedEmptyList, each List.Select(List.Transform(_, Text.From), each not Text.Contains(_, "Machine"))),
    RowNum = List.Numbers((MachineCount-1)*3,MachineCount,-3),
    InsertMachineNum = List.Accumulate({0..List.Count(RowNum)-1}, ListCleanedUp, (x,y)=> List.InsertRange(x,RowNum{y},{{"MACHINE #", SearchKeyword_Machine{y}}})),
    SplitLists = List.Split(InsertMachineNum, MachineCount+1),
    TableFromColumns = List.Transform(SplitLists, each Table.FromColumns(_)),
    CombineTables = Table.Combine(TableFromColumns),
    #"Promoted Headers" = Table.PromoteHeaders(CombineTables, [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([DATE] <> "DATE")),
    #"Filled Down" = Table.FillDown(#"Filtered Rows",{"MACHINE #"})
in
    #"Filled Down"

     

    Output:

     

    Regards
    KT