none
Help with Data manipulation in power query RRS feed

  • Question

  • So I've tried just about everything I can think of to make this work.. I feel like I'm either close or completely thinking about this in the wrong way.

    Here is a subsection of my data set:

    000 006 012
    ITA000174 2 4
    ITA001326
    ITA000159 10

    I can transpose the date either way as well as make either the set of id's starting with "ITA0" or the 3 digit code id's column headers if need be.

    Im trying to get a data set that looks like so:

    ITA000174 2 x 000, 4 x 006
    ITA001326
    ITA000159 10 x 012

    is this even possible?

    Tuesday, October 29, 2019 7:45 PM

Answers

  • Hi greenax,

    you can add a custom column like so:

    let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8gxxNDAwMDQ3UdJRMgJiEJ1XmpOjFKsDlTQ0NjKDCaJScCUGhqaWGEoMDZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, #"0" = _t, #"6" = _t, #"12" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"0", Int64.Type}, {"6", Int64.Type}, {"12", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each

    Text.Combine( List.Transform( List.Skip( List.Zip( {Record.FieldValues(_), Table.ColumnNames(Source)} ) ), (x) => Text.From(x{0}) & " x " & Text.From(x{1})),

    ", ") ) in #"Added Custom"



    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!


    Tuesday, October 29, 2019 8:02 PM
    Moderator

All replies

  • Hi greenax,

    you can add a custom column like so:

    let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8gxxNDAwMDQ3UdJRMgJiEJ1XmpOjFKsDlTQ0NjKDCaJScCUGhqaWGEoMDZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, #"0" = _t, #"6" = _t, #"12" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"0", Int64.Type}, {"6", Int64.Type}, {"12", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each

    Text.Combine( List.Transform( List.Skip( List.Zip( {Record.FieldValues(_), Table.ColumnNames(Source)} ) ), (x) => Text.From(x{0}) & " x " & Text.From(x{1})),

    ", ") ) in #"Added Custom"



    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!


    Tuesday, October 29, 2019 8:02 PM
    Moderator
  • Now would that work if (as the data gets manipulated) more id's starting with "ITA0" or the 3 digit code id's get added or removed?

    Say now I have 5x 3 digit code id's and 6x id's starting with "ITA0"

    000 006 012 013 014
    ITA000174 2 4 12
    ITA001326 5
    ITA000159 3 10
    ITA000160 7 2 1
    ITA000160 1 5 6 2

    Resulting in:

    ITA000174 2 x 000, 4 x 006, 12 x 013
    ITA001326 5 x 012
    ITA000159 3 x 000, 10 x 012
    ITA000160 7 x 006, 2 x 013, 1 x 014
    ITA000160 1 x 000, 5 x 006, 6 x 012, 2 x 013

    Tuesday, October 29, 2019 8:18 PM
  • Yes, absolutely - as long as you only have one column at the start whose values shall not be included in the combination (like it is now).

    You might have to replace the reference to the table name "Source" to the name of the previous step from your query (if it's different).


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!


    Tuesday, October 29, 2019 8:28 PM
    Moderator
  • Awesome! I'm trying to implement it now but I seem to have trouble with this line

       

        #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"000", Int64.Type}, {"6", Int64.Type}, {"12", Int64.Type}}),

    What is the purpose of the Int64.Type? Do I need to have one for each column?

    In my second example would i need to write the line like:

       #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"000", Int64.Type}, {"006", Int64.Type}, {"012", Int64.Type}, {"013", Int64.Type}, {"014", Int64.Type}}),


    Tuesday, October 29, 2019 8:41 PM
  • Coming to think of it, for this example it's better if you skip that step.

    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Tuesday, October 29, 2019 8:44 PM
    Moderator
  • Excel 365 Pro Plus with Power Query.
    Here is a faintly similar array problem
    with text headers and text solutions.
    http://www.mediafire.com/file/qa27a4iq1kz241q/10_29_19.xlsx/file
    http://www.mediafire.com/file/mr140r5t3wm11cw/10_29_19.pdf/file

    Tuesday, October 29, 2019 8:47 PM
  • You are a saint!!

    Thank you!!

    • Edited by greeneax Tuesday, October 29, 2019 9:02 PM
    Tuesday, October 29, 2019 9:02 PM
  • I have a follow-up question - Given a similar table:

    000 006 012 013 014
    ITA000174 2 4 12
    ITA001326 5 2
    ITA000159 3 10
    ITA000160 7 2 1
    ITA000160 1 5 6 2

    Say I now was to take the last column in the above table and subtract it from the next column, with a number, to get the following result. (in the second row we have subtracted 3 from column (012) and in the 4th row we have subtracted 1 from column (013).)

    ITA000174 2 x 000, 4 x 006, 12 x 013
    ITA001326 3 x 012
    ITA000159 3 x 000, 10 x 012
    ITA000160 7 x 006, 1 x 013
    ITA000160 1 x 000, 5 x 006, 6 x 012, 2 x 013


    Is this possible?




    • Edited by greeneax Wednesday, October 30, 2019 1:53 PM
    Wednesday, October 30, 2019 1:52 PM
  • Hi

    IMHO that's a different problem/question...

    Does the following work for you in term of performance (change the Source = … row appropriately)?

    let
        fnPositionOfLastNonNull = (soureList as list) =>
        List.Last(
            List.RemoveNulls(
                List.Transform(List.Positions(soureList),
                    (i)=> if soureList{i} = null then null else i
                )
            )
        ),
    
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        RenamedLastColumn = Table.RenameColumns(Source,{{List.Last(Table.ColumnNames(Source)), "TEMPLast"}}),
        DataColumns = List.RemoveLastN(List.Skip(Table.ColumnNames(RenamedLastColumn),1),1),
        AddedColumn = Table.AddColumn(RenamedLastColumn, "Result",
            (i)=>
                let
                    values = Record.FieldValues(Record.SelectFields(i,DataColumns)),
                    pos = fnPositionOfLastNonNull(values),
                    transformed = List.Transform(List.Positions(values),
                        (j)=>
                            let
                                value = if (j = pos) and (i[TEMPLast] <> null)
                                        then (values{j} - i[TEMPLast])
                                        else values{j},
                                combined = if (value <> null)
                                           then Text.Combine({Text.From(value), DataColumns{j}}," x ")
                                           else null
                            in
                                combined
                    
                    ),
                    combined = Text.Combine(List.RemoveNulls(transformed),", ")
                in
                    combined,
            type text
        ),
        RemovedColumns = Table.SelectColumns(AddedColumn, {List.First(Table.ColumnNames(Source)),"Result"})
    in
        RemovedColumns

    • Edited by Lz._ Friday, November 1, 2019 7:59 AM revised
    Thursday, October 31, 2019 2:13 AM
  • Hi

    Another option

    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        RenamedLastColumn = Table.RenameColumns(Source,{{List.Last(Table.ColumnNames(Source)), "TEMPLast"}}),
        AddedIndex = Table.AddIndexColumn(RenamedLastColumn, "IDX", 0, 1),
        UnpivotedDataColumns = Table.UnpivotOtherColumns(AddedIndex, {"ITA", "TEMPLast", "IDX"}, "ColName", "Value"),
        GroupedOnOtherColumns = Table.Group(UnpivotedDataColumns, {"ITA","IDX"}, {{"GRP", each _, type table}}),
        RemovedIndex = Table.SelectColumns(GroupedOnOtherColumns,{"ITA", "GRP"}),
        TransformedGroup = Table.TransformColumns(RemovedIndex,
            {"GRP", each
                let
                    addIndex = Table.AddIndexColumn(_, "Idx", 1, 1),
                    addColumn = Table.AddColumn(addIndex, "Result",
                        each if [TEMPLast] = null
                                then Text.Combine({Text.From([Value]),[ColName]}," x ")
                             else if ([Idx] = Table.RowCount(addIndex))
                                then Text.Combine({Text.From([Value]-[TEMPLast]),[ColName]}," x ")
                             else Text.Combine({Text.From([Value]),[ColName]}," x "),
                        type text
                    )
                in
                    addColumn,
                type table
            }
        ),
        CombinedNestedResults = Table.AddColumn(TransformedGroup, "Result", each Text.Combine(_[GRP][Result],", ")),
        RemovedGroup = Table.SelectColumns(CombinedNestedResults,{"ITA", "Result"})
    in
        RemovedGroup

    Thursday, October 31, 2019 4:11 PM
  • Hi

    Another option with List.Accumulate (when you have a minute feel free to provide feedback - thanks)

    let
        // Function
        fnListPositionOfLastNonNull = (soureList as list) =>
            List.Accumulate(List.Positions(soureList), null,
                (accum,current)=> if soureList{current} <> null then current else accum
            ),
        //
    
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        ColumnNames = Table.ColumnNames(Source),
        dataColumns = List.RemoveLastN(List.Skip(ColumnNames)),
        AddedColumn = Table.AddColumn(Source, "Result", each
            let
                dataValues = Record.ToList(Record.SelectFields(_, dataColumns)),
                lastColValue = Record.Field(_, List.Last(ColumnNames)),
                posLastDataVal = fnListPositionOfLastNonNull(dataValues),
                result = List.Accumulate(dataColumns, null, (accum,current)=>
                    let
                        currentColPos = List.PositionOf(dataColumns, current),
                        currentValue = if (posLastDataVal = currentColPos) and (lastColValue <> null)
                                       then (dataValues{currentColPos} - lastColValue)
                                       else dataValues{currentColPos}
                    in
                        if currentValue <> null
                        then Text.Combine({accum, Text.Combine({Text.From(currentValue), current}," x ")},", ")
                        else accum
                )
            in
                result,
            Text.Type
        )
    in
        AddedColumn

    Wednesday, November 13, 2019 1:17 AM