Forum Discussion

CarlosMarin11's avatar
CarlosMarin11
New Member
3 years ago

help needed with unpivot columns

 Hi Everyone,

 

I´m seeking help and I´ll try to be as visual as possible while I explain with images:

 

Picture #1: This is the file I need to load directly to a model

PICTURE #1

 

Picture #2: This is how I need it to be at the end of the steps of power Query

PICTURE 2

 

Picture #3: This is the actual end of my steps in power query so far

 

Picture #4: THE ISSUEEE 

 

The rows between the green marks that I have made are the products that I need that beside these columns but I don´t know how to do it since the source file give me the information the way you can see in picture 1.

 

Please help

 

 

 

Need Help

2 Replies

    • jbwtp's avatar
      jbwtp
      Memorable Member
      3 years ago

      Somethign like this:

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg82MDBU0lFSitWJVgIxHMEsIyDLCcwyBrKcwSyQUiNkpS5wpa5wpW5wpcbISt3hSj3gSj2VYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Name", type text}}),
   
   
    trig = (n) => Text.StartsWith(n[Code], "WS"),
    keep = {"Code", "Name"},

     f = (t as table, trigger as function, keepCols as list) =>
        let 
            tbl = Table.Buffer(t), //Consider removal if the code runs slow//
            Pivot = List.Accumulate(Table.ToRecords(tbl), {}, (a, n)=> if  trig(n)
                                                                        then 
                                                                            {Record.SelectFields(n, keepCols) & [Nested = {[]}]} & a 
                                                                        else 
                                                                            {Record.SelectFields(List.First(a), keepCols) & [Nested = List.First(a)[Nested] & {n}]} & List.Skip(a)),
            Extract = Table.FromRecords(Pivot),
            Convert = Table.TransformColumns(Extract,{{"Nested", each Table.FromRecords(List.Skip(_))}})
        in Convert,
  

    out = f(#"Changed Type", trig, keep),
    #"Expanded Nested" = Table.ExpandTableColumn(out, "Nested", {"Code", "Name"}, {"Nested.Code", "Nested.Name"})
in #"Expanded Nested"

      Copy f funciton to your query and then call it in the way demonstrated in the out step.Kind regards,

      John