Forum Discussion

Jedrzej's avatar
Jedrzej
New Member
4 years ago
Solved

Table transformation

Hi, I would like to ask for help with an assignment, today is the first time I am using such advanced PowerQuery functions in this M. I have a graph like this: and I need to get that result:...
Need Help
Show and Tell
  • Jedrzej's avatar
    Jedrzej
    4 years ago

    I solved the problem. The problem was the space character in the LANGUAGE cells because by default they are in one cell as [PL, US, DE]. When split into rows I was getting:[PL], [ US] and [ DE]. Where at the next ones there was a space at the beginning. After removing the space and applying the code:

    Text.Combine(List.FindText(Text.Split([FIRST],"#(lf)"), [LANGUAGE]&":"))&
"#(lf)"&
Text.Combine(List.FindText(Text.Split([SECOND],"#(lf)"), [LANGUAGE]&":"))

    I got the expected result

    thanks for your help!

rohit_singh's avatar
rohit_singh
Solution Sage
4 years ago

Hi Jedrzej ,

Please try the following:

 

1) Assuming your data looks like this, please load this into power query.

 

2) Fill down values on the "NUMBER" and "LANGUAGE" columns

 

3) Merge columns "FIRST" and "SECOND" to create a new column "Merged", using "-" as the delimiter

 

4) Add a custom column that compares "LANGUAGE" with 1st two characters of "Merged". If there is a match it returns the column value of "Merged" else null


5) Remove column "Merged" and filter column custom <> null

 

6) Split column "Custom" by delimiter "-". You will now see two columns "Custom.1" and "Custom.2"


7) Add column to merge values of "Custom.1" and "Custom.2" into a list.

 

😎Remove columns "Custom.1" and "Custom.2" and expand column "Custom"

 

This is the final result

 

Here is the M-Code

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrwARNWUDJWJ1oJyASi0GArBQ9PKCPY1VUh0j8USdrF1Uoh3NPHx9vf19fVDyrg4Roa4gpWZATWSBOjjcEi1Dc6FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NUMBER = _t, LANGUAGE = _t, FIRST = _t, SECOND = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"LANGUAGE"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"NUMBER", Int64.Type}, {"LANGUAGE", type text}, {"FIRST", type text}, {"SECOND", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"NUMBER", "LANGUAGE"}),
    #"Merged Columns" = Table.CombineColumns(#"Filled Down",{"FIRST", "SECOND"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
    #"Added Custom" = Table.AddColumn(#"Merged Columns", "Custom", each if [LANGUAGE] = Text.Range([Merged],0,2) then [Merged] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Merged"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Custom] <> null)),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Custom", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Custom.1", "Custom.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each {[Custom.1],[Custom.2]}),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Custom.1", "Custom.2"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns1", "Custom")
in
    #"Expanded Custom"

 

 

Kind regards,

Rohit


Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

  • Jedrzej's avatar
    Jedrzej
    New Member
    4 years ago

    Unfortunately, my initial data looks different.

    NUMBERLANGUAGE FIRSTSECOND
    1PL,US,DEPL:
    US: HI
    DE: WILLKOMMEN    		PL:
    US: SEE YOU
    DE: HEUTE

    in the LANGUAGE column, I separate to the rows after the "," and I get:

    NUMBERLANGUAGEFIRSTSECOND
    1PLPL:
    US: HI
    DE: WILLKOMMEN    		PL:
    US: SEE YOU
    DE: HEUTE
    1USPL:
    US: HI
    DE: WILLKOMMEN    		PL:
    US: SEE YOU
    DE: HEUTE
    1DEPL:
    US: HI
    DE: WILLKOMMEN    		PL:
    US: SEE YOU
    DE: HEUTE

    In the FIRST and SECOND columns, sentences are separated by the end-of-line symbol (#(lf)).

    The contents of one FIRST cell is:
    [EN: #(lf) US: HI #(lf) DE: WILLKOMMEN]

    the problem is how to select the appropriate language versions from a single cell.

    unfortunately splitting it into more rows like in the case of LANGUAGE is not an option, because it will extend the number of rows by about 5^30

  • Jedrzej's avatar
    Jedrzej
    New Member
    4 years ago

    I solved the problem. The problem was the space character in the LANGUAGE cells because by default they are in one cell as [PL, US, DE]. When split into rows I was getting:[PL], [ US] and [ DE]. Where at the next ones there was a space at the beginning. After removing the space and applying the code:

    Text.Combine(List.FindText(Text.Split([FIRST],"#(lf)"), [LANGUAGE]&":"))&
"#(lf)"&
Text.Combine(List.FindText(Text.Split([SECOND],"#(lf)"), [LANGUAGE]&":"))

    I got the expected result

    thanks for your help!