Need help with power query unpivoting

[kamranmumtaz02]

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?

[KT_Bsmart2gethe]

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

[v-yanjiang-msft]

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]

@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.

[ImkeF]

Hi @kamranmumtaz02 ,

I don't understand your request.

Please read the guidelines on how to post your questions to get them answered quickly and correctly: help how to get your questions answered quickly - Microsoft Power BI Community