none
Cost Centre Hiererchy RRS feed

  • Question

  • CostCentreHierarchy

    I export a cost centre hierarchy from SAP to excel. I need to covert this hierarchy into tabular format for using as a look up table in my data model. Currently I use macro for this conversion. I like to use PQ for this transformation. I have attached the link above. Hopefully the link is working. I want this to be dynamic, which I can do this for the whole organisation or a division. I can send the complete output that I get from using VBA for clarification.

    CC Hierarchy 

    Tuesday, March 5, 2019 10:08 AM

Answers

  • Hi M.Awal:

    That step transforms a list of words (like: {"Imke", "M.Awal"} ) into a nested list of characters:

    { {"I", "m", "k", "e"}, {"M", ".", "A", "w", "a", "l"} }

    To do this, the words will be spilt into a list of characters using the Text.ToList-function. But before doing so, I transform each list element into text-format to make sure the function doesn't fail at numbers (Text.From)

    I'm using "(x) =>" instead of "each", because "each" is used in the formula already. So references to fields wouldn't be unambiguous any more.

    Hope this answers your first question?

    Unfortunately I don't understand what you mean with your 2nd question. Or is there a picture that explains it that I can currently not see?

    What do you mean with "level below" and what do you mean with "name my column headers" (you want to rename them? All? What shall be the new names?)?


    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!

    • Marked as answer by M.Awal Monday, June 3, 2019 11:30 AM
    Monday, April 8, 2019 11:34 AM
    Moderator
  • Hi Imke:

    I have pasted a screen shot for some reason it got disappeared. Anyway, In the 11th step "LevelLookup" you have determined how many levels the hierarchy contains. Can we add two steps 1) "Level"&[Level] then (2) transpose it to rows. After call this step in the end to use it as column headers by appending this row with the rest of the data.

    When you use a number inside the curly bracket (e.g List.Count(x) = 7){0}), are you referring to the nth record or row? 

    Thanks

    • Marked as answer by M.Awal Monday, June 3, 2019 11:30 AM
    Tuesday, April 9, 2019 10:03 AM
  • The curly brackets are positional index operator in the M-language. They can be applied to every object that has more than one item in them (tables and lists for example) https://docs.microsoft.com/en-gb/powerquery-m/evaluation-model

    What you're trying to achieve is dynamic renaming of columns. I'm not quite sure yet on which columns you want to apply the 13 new names (there are more than that in your actual table), but this video might get you starting: https://www.youtube.com/watch?v=yBJr0sAc-m4



    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!

    • Marked as answer by M.Awal Monday, June 3, 2019 11:30 AM
    Thursday, April 11, 2019 6:12 AM
    Moderator
  • If the source data shall sit in the same workbook than the result, is has to be converted into a table first. 

    But I doubt that this is what makes is slow. Performance-tuning can become a very complex and time consuming task (nothing I do in the forums for free any more). The tips on this site might help you: https://www.thebiccountant.com/speedperformance-aspects/


    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!

    • Marked as answer by M.Awal Monday, June 3, 2019 11:30 AM
    Thursday, April 11, 2019 6:15 AM
    Moderator
  • Thanks. Very much appreciated for your help and the work that you doing for the community.
    • Marked as answer by M.Awal Thursday, April 11, 2019 8:50 PM
    Thursday, April 11, 2019 8:50 PM

All replies

  • Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Interpret hierarchy symbols.
    http://www.mediafire.com/file/3l54oaje3nmuzu2/03_05_19.xlsx/file
    http://www.mediafire.com/file/w0eviggllanyjm4/03_05_19.pdf/file

    Wednesday, March 6, 2019 11:21 PM
  • Thanks for your response. Is it possible to have the out similar to the one in the link below.

    Outputfile

    • In term of creating different levels dynamically, it depends on the “space”  characters in between the | signs. There are leading and trailing spaces that needs to be get rid off.
    • For the cost centre field, the cost centre number should be picked up any numbers that has seven digits.
    • Cost Centre Name should be the text after the space as shown below.


    Thursday, March 7, 2019 11:10 AM
  • Sorry I meant to say "output".
    Thursday, March 7, 2019 11:11 AM
  • I interpreted the number of spaces between the | signs in a way
    to achieve an arbitrary, elegant data configuration.
    Tweak the M code to achieve your own idiosyncratic configuration.
    My solution is a mere nudge.

    Thursday, March 7, 2019 2:50 PM
  • Hi Herbert,

    "My solution is a mere nudge."

    So slight of a nudge that it requires a massive shove from there. However, if you think that a mere tweak is all that's required, then you should provide the full solution. I would certainly be interested. I could find no discernable pattern at all in the data to even begin to think of how to generate the required output.

    There's another post that requested the breakdown of a hierarchy in a column. That was easily solvable because each top level of the hierarchy was followed by its lower levels, where the levels were identified by the amount of indentation in the column.


    • Edited by Colin Banfield Thursday, March 7, 2019 6:33 PM Changed Howard to Herbert
    Thursday, March 7, 2019 6:32 PM
  • Colin,
    The OP's desired output "OutputFile"
    is based on different source data
    than the original data of "CC Hierarchy".
    None of the posted required outputs make any sense to me.
    So I ignored them and came up with a scheme that
    follows the coded original hierarchy indents and uses all the data,
    (adding some aesthetic liberty and some common business sense).
    I consider my posted files full solutions of the OPs source data.
    It was quite a bit of work.
    Regards, Herbert (aka Howard)

    Friday, March 8, 2019 4:46 AM
  • Hi Herbert:

    The output that I presented above can be achieved from data source above. I did it myself. But the problem in my solution is when I perform the split column by delimiter it gets hard coded and I want it to work dynamically. Meaning I want the coding to work for the whole organisation structure or for a single division. I think list.generate is the main ingredient for this solution but I haven't mastered it yet.

    Anyway, SAP produces the source data is two formats the one is in the link in my previous post the other is below with the macro solution that I currently use to convert into tabular format

    CC Hierarchy Solution

    Friday, March 8, 2019 9:30 AM
  • Regards, Herbert (aka Howard)

    :D

    Friday, March 8, 2019 2:37 PM
  • Hi M.Awal,

    I think list.generate is the main ingredient for this solution but I haven't mastered it yet.

    Well I have long mastered List.Generate, List.Accumulate, and generic looping forms(tail and non-tail recursion). I've done several dynamic hierarchy expansion projects, including employee/manager level expansion, account parent/child level expansion, and bill of material (BOM) explosions. I've done a hierarchy expansion with data in a single column. I have been using Power Query for over five years. And yet, with all of that experience, I am not smart enough to solve your problem.

    There are very smart folks around here (including Horward, er... Herbert). It's possible that some of there folks are working on a possible solution.

    Until such time, I encourage you to master List.Generate, and post your solution so that we can all learn something new.


    Friday, March 8, 2019 3:00 PM
  • Hi Colins: Thanks for your effort to assist me. Greatly appreciated. I know there are a number of smart people who helps out with PQ coding. Hence, I am here. SAP is a popular ERP system in the world. I thought may be other people might have encountered this problem and solved it in PQ.
    Friday, March 8, 2019 10:04 PM
  • Hi M.Awal,

    please check this code:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", type any}, {"Column5", type text}, {"Column6", type any}, {"Column7", type text}, {"Column8", Int64.Type}, {"Column9", type text}, {"Column10", Int64.Type}, {"Column11", type text}, {"Column12", Int64.Type}, {"Column13", type text}, {"Column14", type any}, {"Column15", type text}, {"Column16", type any}, {"Column17", type text}, {"Column18", type any}, {"Column19", type text}, {"Column20", type any}, {"Column21", type text}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}}),
        AllFields = Table.AddColumn(#"Changed Type", "AllFields", each List.RemoveNulls(Record.FieldValues(_))),
        FieldList = Table.AddColumn(AllFields, "FieldList", each List.Transform([AllFields], (x) => Text.ToList(Text.From(x)))),
        #"Added Custom" = Table.AddColumn(FieldList, "7DigitNumbers", each List.Product(List.Transform(List.Select([FieldList], (x) => List.Count(x) = 7){0}, Number.From))),
        #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"7DigitNumbers", null}}),
        #"Replaced Value" = Table.ReplaceValue(#"Replaced Errors",each [AllFields]{0} ,null,Replacer.ReplaceValue, Table.ColumnNames(Source)),
        Replace2 = Table.ReplaceValue(#"Replaced Value",each if [7DigitNumbers] = null then null else [AllFields]{1} ,null,Replacer.ReplaceValue, Table.ColumnNames(Source)),
        #"Filled Down" = Table.FillDown(Replace2,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28"}),
        #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([7DigitNumbers] <> null)),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"FieldList", "7DigitNumbers"}),
        #"Added Custom3" = Table.AddColumn(#"Removed Columns", "RowValues", each List.RemoveNulls(List.Difference(Record.FieldValues(_), {List.Last(Record.FieldValues(_))}))),
        #"Added Custom4" = Table.AddColumn(#"Added Custom3", "FillUp", each [RowValues] & List.Repeat({List.Last([RowValues])}, 13-List.Count([RowValues]))),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Custom4",{"FillUp", "AllFields"}),
        #"Added Custom1" = Table.AddColumn(#"Removed Other Columns", "Account Centre", each [AllFields]{0}),
        #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Account Centre Description", each [AllFields]{1}),
        #"Added Custom5" = Table.AddColumn(#"Added Custom2", "Expand", each Table.FromRows({[FillUp]})),
        #"Removed Columns1" = Table.RemoveColumns(#"Added Custom5",{"FillUp", "AllFields"}),
        #"Expanded Expand" = Table.ExpandTableColumn(#"Removed Columns1", "Expand", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13"})
    in
        #"Expanded Expand"

    To me it looks as what you're looking for.


    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!

    Thursday, March 28, 2019 12:15 AM
    Moderator
  • Hi Imke,

    The source table has a single column (Column1). How did you get 28 columns?


    Thursday, March 28, 2019 2:15 AM
  • I took M.Awals file from 8th March. 

    It was late yesterday, so I didn't read the other posts. But the 1-column-source is even better, not so heavyweight transformations:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Indent", each Text.PositionOf([Column1], "|", Occurrence.Last)),
        #"Added Index1" = Table.AddIndexColumn(#"Added Custom", "Index", 0, 1),
        #"Added Custom1" = Table.AddColumn(#"Added Index1", "Custom.1", each List.First(List.Skip(List.Reverse(Text.Split([Column1], "   "))))),
        #"Inserted Text After Delimiter" = Table.AddColumn(#"Added Custom1", "Numbers", each Text.AfterDelimiter([Custom.1], "-", {0, RelativePosition.FromEnd}), type text),
        #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Text After Delimiter",{{"Numbers", Int64.Type}}),
        #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type1", {{"Numbers", null}}),
        Custom1 = List.Distinct(#"Replaced Errors"[Indent]),
        #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        Positions = Table.AddIndexColumn(#"Converted to Table", "Index", 1, 1),
        #"Merged Queries" = Table.NestedJoin(#"Replaced Errors",{"Indent"},Positions,{"Column1"},"Positions",JoinKind.LeftOuter),
        #"Expanded Positions" = Table.ExpandTableColumn(#"Merged Queries", "Positions", {"Index"}, {"Position"}),
        #"Inserted Text After Delimiter1" = Table.AddColumn(#"Expanded Positions", "Text After Delimiter", each if [Numbers] = null then Text.AfterDelimiter([Column1], "      ", {0, RelativePosition.FromEnd}) else null, type text),
        #"Replaced Value" = Table.ReplaceValue(#"Inserted Text After Delimiter1","|","",Replacer.ReplaceText,{"Text After Delimiter"}),
        #"Cleaned Text" = Table.TransformColumns(#"Replaced Value",{{"Text After Delimiter", Text.Clean, type text}}),
        #"Trimmed Text" = Table.TransformColumns(#"Cleaned Text",{{"Text After Delimiter", Text.Trim, type text}}),
        #"Replaced Value2" = Table.ReplaceValue(#"Trimmed Text","",null,Replacer.ReplaceValue,{"Text After Delimiter"}),
        #"Added Custom3" = Table.AddColumn(#"Replaced Value2", "Custom", each Text.Combine(List.Repeat({[Text After Delimiter]},14-[Position]), "|")),
        #"Added Custom2" = Table.AddColumn(#"Added Custom3", "Level", each Text.Repeat("|",[Position]-1) & [Custom]),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom2", "Level", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Level.1", "Level.2", "Level.3", "Level.4", "Level.5", "Level.6", "Level.7", "Level.8", "Level.9", "Level.10", "Level.11", "Level.12", "Level.13"}),
        #"Replaced Value1" = Table.ReplaceValue(#"Split Column by Delimiter","",null,Replacer.ReplaceValue,{"Level.1", "Level.2", "Level.3", "Level.4", "Level.5", "Level.6", "Level.7", "Level.8", "Level.9", "Level.10", "Level.11", "Level.12", "Level.13"}),
        #"Filled Down" = Table.FillDown(#"Replaced Value1",{"Level.1", "Level.2", "Level.3", "Level.4", "Level.5", "Level.6", "Level.7", "Level.8", "Level.9", "Level.10", "Level.11", "Level.12", "Level.13"}),
        #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Indent", "Index", "Custom.1", "Position", "Text After Delimiter", "Custom"}),
        #"Extracted Text After Delimiter" = Table.TransformColumns(#"Removed Columns", {{"Column1", each Text.AfterDelimiter(_, "   ", {0, RelativePosition.FromEnd}), type text}}),
        #"Filtered Rows" = Table.SelectRows(#"Extracted Text After Delimiter", each ([Numbers] <> null)),
        #"Trimmed Text1" = Table.TransformColumns(#"Filtered Rows",{{"Column1", Text.Trim, type text}})
    in
        #"Trimmed Text1"


    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!

    Thursday, March 28, 2019 10:20 AM
    Moderator
  • Actually, I believe there is an error in my first solution (with 28 columns). It should be like so:

    let Table2_Table= Excel.CurrentWorkbook(){[Name="Table2"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(Table2_Table,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", type any}, {"Column5", type text}, {"Column6", type any}, {"Column7", type text}, {"Column8", Int64.Type}, {"Column9", type text}, {"Column10", Int64.Type}, {"Column11", type text}, {"Column12", Int64.Type}, {"Column13", type text}, {"Column14", type any}, {"Column15", type text}, {"Column16", type any}, {"Column17", type text}, {"Column18", type any}, {"Column19", type text}, {"Column20", type any}, {"Column21", type text}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}}),

    AllFields = Table.AddColumn(#"Changed Type", "AllFields", each List.RemoveNulls(Record.FieldValues(_))), FieldList = Table.AddColumn(AllFields, "FieldList", each List.Transform([AllFields], (x) => Text.ToList(Text.From(x)))), #"Added Custom" = Table.AddColumn(FieldList, "7DigitNumbers", each List.Product(List.Transform(List.Select([FieldList], (x) => List.Count(x) = 7){0}, Number.From))), #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"7DigitNumbers", null}}), #"Added Custom2" = Table.AddColumn(#"Replaced Errors", "Custom", each List.PositionOf(Record.FieldValues(_), List.Last([AllFields]))), #"Filtered Rows1" = Table.SelectRows(#"Added Custom2", each ([Custom] <> 0)), LevelLookUp = Table.AddIndexColumn(Table.FromList(List.Distinct(#"Filtered Rows1"[Custom]), Splitter.SplitByNothing()), "Level",1,1), #"Merged Queries1" = Table.NestedJoin(#"Filtered Rows1",{"Custom"},LevelLookUp,{"Column1"},"LevelLookUp",JoinKind.LeftOuter), #"Expanded LevelLookUp" = Table.ExpandTableColumn(#"Merged Queries1", "LevelLookUp", {"Level"}, {"Level"}), #"Added Custom3" = Table.AddColumn(#"Expanded LevelLookUp", "Account Centre Description", each [AllFields]{1}), #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Account Centre", each [AllFields]{0}), #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom4",{"Account Centre", "Account Centre Description", "Level", "7DigitNumbers"}), #"Added Custom1" = Table.AddColumn(#"Removed Other Columns1", "Custom", each Text.Combine(List.Repeat({[Account Centre Description]},14-[Level]), "|")), #"Added Custom5" = Table.AddColumn(#"Added Custom1", "Custom.1", each Text.Repeat("|",[Level]-1) & [Custom]), #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom5", "Custom.1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv)), #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter","",null,Replacer.ReplaceValue,{"Custom.1.1", "Custom.1.2", "Custom.1.3", "Custom.1.4", "Custom.1.5", "Custom.1.6", "Custom.1.7", "Custom.1.8", "Custom.1.9", "Custom.1.10", "Custom.1.11", "Custom.1.12", "Custom.1.13"}), #"Filled Down" = Table.FillDown(#"Replaced Value",{"Custom.1.1", "Custom.1.2", "Custom.1.3", "Custom.1.4", "Custom.1.5", "Custom.1.6", "Custom.1.7", "Custom.1.8", "Custom.1.9", "Custom.1.10", "Custom.1.11", "Custom.1.12", "Custom.1.13"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([7DigitNumbers] <> null)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Level", "7DigitNumbers", "Custom"}) in #"Removed Columns"



    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!



    Thursday, March 28, 2019 10:43 AM
    Moderator
  • Hi Imke: Thank you very very much for putting in effort to find a solution for me. The data that you used for the whole organisation and it works like a charm. I need to test if it works dynamically say if I am doing it only for a division. I will come back you if I have any issue with that.

    Now I would like to understand your coding. Can you please clarify bit more what the follow steps are doing?

       AllFields = Table.AddColumn(#"Changed Type", "AllFields", each List.RemoveNulls(Record.FieldValues(_))),
       
    FieldList = Table.AddColumn(AllFields, "FieldList", each List.Transform([AllFields], (x) => Text.ToList(Text.From(x)))),
       
    #"Added Custom" = Table.AddColumn(FieldList, "7DigitNumbers", each List.Product(List.Transform(List.Select([FieldList], (x) => List.Count(x) = 7){0}, Number.From))),

    Friday, March 29, 2019 10:00 AM
  • Hi Imke : In the output, when I filter "QFES" in level -2. I don't get "QFES Distr 1- Northern as per below.

    Thanks

    Friday, March 29, 2019 10:21 AM
  • For some reason my screen shots are not showing. After you filter level-2 as per above then it should be "QFES Distr 1- Northern" from level-10 onward.

    Thanks

    Friday, March 29, 2019 10:32 AM
  • Hi Imke

    I got excited too early. There are some issues with level-4 onwards. It doesn't line up the same way with my desired output. Cheers

    Friday, March 29, 2019 10:43 AM
  • Hi M.Awal,

    I actually didn't check thoroughly, so there was a missing condition in step   #"Added Custom5"

    I also had to add an index-column, as PQ was screwing up the sort order of the first rows.

    Please find the new file here:  https://1drv.ms/x/s!Av_aAl3fXRbehcUjWEo_9auh093txw

    Query "CheckTableIdentity" shows that both table contents are identical now ;)

    The query is not dynamic with regards to the number of columns, as they are hardcoded.

    It would also not work, if the account numbers aren't in a consistent 7-digit-number-format.

    I would suggest that you try to rebuild the functions from inside out (like in Excel) to see step-by-step what they do. That's the best way to learn these things.

    Cheers, Imke


    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!

    Friday, March 29, 2019 1:24 PM
    Moderator
  • Hi Imke: Can you please let me know what is the use of this function (x)=> inside the List.Transformation

    = Table.AddColumn(AllFields, "FieldList", each List.Transform([AllFields], (x) => Text.ToList(Text.From(x))))

    Also is there a way I can use the level below to name my column headers.

    = Table.AddIndexColumn(Table.FromList(List.Distinct(#"Added Index"[Custom]), Splitter.SplitByNothing()), "Level",1,1)

    Monday, April 8, 2019 10:45 AM
  • Hi M.Awal:

    That step transforms a list of words (like: {"Imke", "M.Awal"} ) into a nested list of characters:

    { {"I", "m", "k", "e"}, {"M", ".", "A", "w", "a", "l"} }

    To do this, the words will be spilt into a list of characters using the Text.ToList-function. But before doing so, I transform each list element into text-format to make sure the function doesn't fail at numbers (Text.From)

    I'm using "(x) =>" instead of "each", because "each" is used in the formula already. So references to fields wouldn't be unambiguous any more.

    Hope this answers your first question?

    Unfortunately I don't understand what you mean with your 2nd question. Or is there a picture that explains it that I can currently not see?

    What do you mean with "level below" and what do you mean with "name my column headers" (you want to rename them? All? What shall be the new names?)?


    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!

    • Marked as answer by M.Awal Monday, June 3, 2019 11:30 AM
    Monday, April 8, 2019 11:34 AM
    Moderator
  • Hi Imke:

    I have pasted a screen shot for some reason it got disappeared. Anyway, In the 11th step "LevelLookup" you have determined how many levels the hierarchy contains. Can we add two steps 1) "Level"&[Level] then (2) transpose it to rows. After call this step in the end to use it as column headers by appending this row with the rest of the data.

    When you use a number inside the curly bracket (e.g List.Count(x) = 7){0}), are you referring to the nth record or row? 

    Thanks

    • Marked as answer by M.Awal Monday, June 3, 2019 11:30 AM
    Tuesday, April 9, 2019 10:03 AM
  • Hi

    Pushing the source data through excel table into power query makes the query run very slow. Is there any workaround to it? I want the source data in the same workbook as the output from PQ.

    Tuesday, April 9, 2019 10:17 AM
  • The curly brackets are positional index operator in the M-language. They can be applied to every object that has more than one item in them (tables and lists for example) https://docs.microsoft.com/en-gb/powerquery-m/evaluation-model

    What you're trying to achieve is dynamic renaming of columns. I'm not quite sure yet on which columns you want to apply the 13 new names (there are more than that in your actual table), but this video might get you starting: https://www.youtube.com/watch?v=yBJr0sAc-m4



    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!

    • Marked as answer by M.Awal Monday, June 3, 2019 11:30 AM
    Thursday, April 11, 2019 6:12 AM
    Moderator
  • If the source data shall sit in the same workbook than the result, is has to be converted into a table first. 

    But I doubt that this is what makes is slow. Performance-tuning can become a very complex and time consuming task (nothing I do in the forums for free any more). The tips on this site might help you: https://www.thebiccountant.com/speedperformance-aspects/


    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!

    • Marked as answer by M.Awal Monday, June 3, 2019 11:30 AM
    Thursday, April 11, 2019 6:15 AM
    Moderator
  • I would appreciate if you could mark my solution as solution for this thread.

    Thanks.


    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!

    Thursday, April 11, 2019 1:18 PM
    Moderator
  • Thanks. Very much appreciated for your help and the work that you doing for the community.
    • Marked as answer by M.Awal Thursday, April 11, 2019 8:50 PM
    Thursday, April 11, 2019 8:50 PM