Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
Hi @Ade1991 ,
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @Ade1991 ,
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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)
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)