Results 1 to 11 of 11

Pivot repeating columns names in PQ

Threaded View

  1. #7
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,222

    Re: Pivot repeating columns names in PQ

    Please try

    let
        Source = Excel.CurrentWorkbook(){[Name="CSV_Import"]}[Content],
        #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv)),
        #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
        #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Quantity1", "Quantity bad1", "Rejected1", "Total area1", "Order number2", "Part number2", "Quantity2", "Quantity bad2", "Rejected1_1", "Total area2", "Order number3", "Part number3", "Quantity3", "Quantity bad3", "Rejected1_2", "Total area3", "Order number4", "Part number4", "Quantity4", "Quantity bad4", "Rejected1_3", "Total area4", "Following alarm exist:"}),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Date", "Date in", "Date out", "Flightbar number", "Process number", "Order number1", "Part number1"}, "Attribute", "Value"),
        #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.BeforeDelimiter(_, "_"), type text}}),
        #"Added Index" = Table.AddIndexColumn(#"Extracted Text Before Delimiter", "Index", 0, 1, Int64.Type),
        #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Date", "Date in", "Date out", "Flightbar number", "Process number", "Order number1", "Part number1", "Attribute", "Value"}),
        #"Integer-Divided Column" = Table.TransformColumns(#"Reordered Columns", {{"Index", each Number.IntegerDivide(_, 7), Int64.Type}}),
        #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Attribute]), "Attribute", "Value"),
        #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index", ""}),
        #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"Time down", type time}, {"Time up", type time}})
    in
        #"Changed Type"
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Move Repeating and Non-Repeating Names into separated columns
    By aaaaa34 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-23-2019, 03:02 PM
  2. Replies: 7
    Last Post: 12-05-2016, 10:23 AM
  3. Replies: 5
    Last Post: 07-14-2016, 10:06 AM
  4. pick a name out of list of 20 names without repeating names picked
    By gammoneer2b in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-06-2014, 10:14 AM
  5. Replies: 3
    Last Post: 07-28-2014, 03:48 AM
  6. Replies: 1
    Last Post: 06-20-2012, 08:14 AM
  7. repeating names in row fields of a pivot table
    By Jack N in forum Excel General
    Replies: 1
    Last Post: 08-18-2006, 11:40 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1