Debug of M Function - Unpivot Many rows

[Ade1991]

Hi,

I found this topic :

from the BIccountant blog (hello @ImkeF )

It's about unpivoting many rows.

I'm using the script from Bill Szys (end of the article).

let
    Source = (t as table, FirstNColumnsToKeep as number, FirstNRowsToSkip as number)=>


let
// ------------------------------------------------------------------------------------------------------------------------
// This part creates a fake, one row table with column names like in original table  ( first row (the only row) will be promoted later )

            Tab = Table.FirstN(t, FirstNRowsToSkip),
            Custom1 = Table.Transpose(Tab),
            ChangedType = Table.TransformColumnTypes(Custom1,List.Transform(Table.ColumnNames(Custom1), each {_, type text})),
            AllColumnNames = Table.ColumnNames(Tab),
            ConstColumnNames = List.FirstN(AllColumnNames,FirstNColumnsToKeep),
            ListOfTransposedColumnNames = Table.ColumnNames(ChangedType),

//             ------------------------------------
// The line below is not necessary if null is real null value and not "null" (like in Table1)


            #"Replaced Value" = Table.ReplaceValue(ChangedType,"null",null,Replacer.ReplaceValue, ListOfTransposedColumnNames),

//             -----------------------------------

            ReadyToMerge = Table.FillDown(#"Replaced Value", ListOfTransposedColumnNames),
            #"Merged Columns" = Table.CombineColumns(ReadyToMerge, ListOfTransposedColumnNames, Combiner.CombineTextByDelimiter("$_$", QuoteStyle.None),"MergedColumns"),
            #"Filtered Rows" = Table.Skip(#"Merged Columns", FirstNColumnsToKeep)[MergedColumns],
            AllNewNames = Table.FromRows({ConstColumnNames & #"Filtered Rows"}, AllColumnNames),
// -------------------------------------------------------------------------------------------------------------------------

// This part combines fake table (AllNewNames) and original table (t) without FirstNRowsToSkip and then it does a few (easy) necessary things

    Combine = Table.Combine( {AllNewNames, Table.Skip(t, FirstNRowsToSkip)}),
    #"Promoted Headers" = Table.PromoteHeaders(Combine, [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", ConstColumnNames, "Attrib", "Value"),
    ListOfSplitedColumnNames = List.Transform({1..FirstNRowsToSkip}, each "Attrib." & Text.From(_) ),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns","Attrib",Splitter.SplitTextByDelimiter("$_$", QuoteStyle.Csv),ListOfSplitedColumnNames),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter", List.Transform(ListOfSplitedColumnNames, each {_, type text}))
in
    #"Changed Type"

// Documentation ( I've stolen this part from You Imke;-))	
	, documentation = [
Documentation.Name =  " fxUnpivBill
", Documentation.Description = " Unpivots a table according to the number of columns and header rows passed on in the parameters
" , Documentation.LongDescription = " Unpivots a table according to the number of columns and header rows passed on in the parameters
", Documentation.Category = " Table functions
", Documentation.Source = " local
", Documentation.Author = " Bill Szysz
", Documentation.Examples = {[Description =  " 
" , Code = " Look at the code in Advanced Editor to read some notes
 I've stolen this part (Documentation part) from You Imke ;-)
 ", Result = " 
"]}] 
 in 
  Value.ReplaceType(Source, Value.ReplaceMetadata(Value.Type(Source), documentation))

The script works perfectly when I call it as an independant function

However, when I try to use it in an "applied step", using "customised function", it doesn't work, with error message : cyclical function 

Where does the problem come from ?

Thanks

Ade

[ImkeF · Accepted solution]

Hi @Ade1991 ,

[artemus]

In your step, change Initial to your previous step. Since your previous step has spaces in it, make sure you have #"" around it like:

= MyFunction(#"Previous Step Name", 1, 2)

[Ade1991]

@ImkeF 

Thanks for the answer, but not sure to understand.

1) Do i have to change the name of the table (then create a new one) to apply the function ?

2) Remplir vers le bas = fill down (i apply this step before to use the M Script)